Using VLOOKUP to locate an answer in one of multiple columns

[ksean]

I am using the following formula to get an answer from a single column of a
spread sheet (column 2) =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE)

Would you please tell me how I should modify the formula to look in more
than one column for the appropriate answer? (i.e. look in columns #2, #9, and
#22)

If possible I would also like the formula to make the answer cell blank if
it is unable to locate an appropriate answer.

Thanks.

 

[Kassie]

To answer your last question first, insert the following bit just after the =
IF(VLOOKUP(B3,Master!$R$3:$QP$4180,2,0),"",

Do you want the formula to look in other columns if it does not find
anything in the first location, or what? How would VLOOKUP know where to
look?

If the first, use the following formula

=IF(NOT(ISERROR(VLOOKUP(B3,MASTER!$R$3:$QP$4180,2,0))),VLOOKUP(B3,MASTER!$R$3:$QP$4180,2,0),IF(NOT(ISERROR(VLOOKUP(B3,MASTER!$R$3:$QP$4180,9,0))),VLOOKUP(B3,MASTER!$R$3:$QP$4180,9,0),IF(NOT(ISERROR(VLOOKUP(B3,MASTER!$R$3:$QP$4180,22,0))),VLOOKUP(B3,MASTER!$R$3:$QP$4180,22,0),""))).
It must all be on one line though!

By the way, can you not name the range you are referring to? MainData, iso
MASTER!$R$3:$QP$4180, would work a lot better!
=IF(NOT(ISERROR(VLOOKUP(B3,MainData,2,0))),VLOOKUP(B3,MainData,2,0),IF
(NOT(ISERROR(VLOOKUP(B3,MainData,9,0))),VLOOKUP(B3,MainData,9,0),IF(NOT(ISERROR(VLOOKUP(B3,MainData,22,0))),VLOOKUP(B3,MainData,22,0),"")))

--
HTH

Kassie

Replace xxx with hotmail