Excel dynamic column value based on other columns

0
votes

I have an Excel spreadsheet where the A column is a list of email address and columns C-P represent VLOOKUP formulas to return a phone number. Each column represents a different source.
C-P columns will only contain one value and the rest will be #N/A. How do I fill the B column with the valid value from the C-P columns, assuming there is one?

I tried to do a formula using nested IFNAs but it seems to be the case that Excel only allows up to 8 nested if statements, and there are more than 8 columns in my spreadsheet so that is not a valid solution for my problem.

I believe this should be possible using formulas alone so I was hoping to find a solution that accomplishes that. Does anyone have any ideas on how I may be able to approach this problem?

1
excel

1 Answers

1
votes

Like the SUMPRODUCT function, the newer AGGREGATE function can provide cyclic processing with some of its sub-functions but also discard errors.

=IFERROR(INDEX(C2:P2, AGGREGATE(15, 6, COLUMN(A:O)/SIGN(LEN(C2:P2)), 1)), "")

I've added an IFERROR function wrappe to avoid another #N/A in case 'assuming there is one' is not correct.