I have a column with phone numbers and another one in which I use LOOKUPV to look up the same phone numbers in another file (I use this file to look up some missing numbers, but I don`t have all of them). In a third column, I want to use a function to treat these phone numbers (they need to be formatted in a certain way), but my problem is that in some cases I have lines in which the phone number in column A is present and the number in column B is not, or vice versa. I want to use a function which looks up a number in both columns and returns only one phone number. How can this be done? Example:

Thank you!

CodePudding user response：

You can use the if function to check which column is not empty and return it's value.

=if(A2="",B2,A2)

Or

=if(LEN(A2)=0,B2,A2)

CodePudding user response：

Assume the data starts in cell A2, put this in cell C2:

=if(and(A2="",B2=""),"error",if(A2="",B2,A2))

If you have the ID in col A and the phone numbers in B and C, then something like:

=iferror(vlookup(D2,A2:C10,2,0),vlookup(D2,A2:C10,3,0)

This assumes that both B2 and C2 will not be empty.