Finding a value or the one next to it in a range NOT array

[advanced novice]

Picture data in a phone book with 5 columns of names and numbers 10 cols
total. I need a function that will look up the name and return the number
that will work if I don't know in advance which column the name will be in.
(they aren't in alpha order). Vlookup and match presume I know which column I
am dealing with. Help this has taken many unprofitable hours from my life.

 

[Pete_UK]

So, do you mean you have names in columns A, C, E, G and I and numbers
in columns B, D, F, H and J? The name you are looking for could be
anywhere in those 5 columns (or maybe not present at all)?

Pete

 

[advanced novice]

Exactly, except in my case I know the name will always be there, but it is
possible the number will have a value of 0. Thank you for attemping to answer
this!

 

[Pete_UK]

Okay, I'm assuming you are entering a name into cell A1 on Sheet2 and
you want your answer returned to cell B1, and that your data is on
Sheet1. Put this formula in B1:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"",VLOOKUP(A1,Sheet1!A:B,
2,0))&IF(ISNA(MATCH(A1,Sheet1!C:C,0)),"",VLOOKUP(A1,Sheet1!C,
2,0))&IF(ISNA(MATCH(A1,Sheet1!E:E,0)),"",VLOOKUP(A1,Sheet1!E:F,
2,0))&IF(ISNA(MATCH(A1,Sheet1!G:G,0)),"",VLOOKUP(A1,Sheet1!G:H,
2,0))&IF(ISNA(MATCH(A1,Sheet1!I:I,0)),"",VLOOKUP(A1,Sheet1!I:J,2,0))

Hope this helps.

Pete