The question is how do you identify the 'appropriate answer' for the vlookup.
When you do a look up, you are looking up a value in an array, so if the
value does not exist in the first column of the array, then column number is
irrelvent. For example:

=VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE)

Say that B3 equals "PJ". If PJ does not appear in column R of your array
R3:AP4180, then picking a new column won't yield any results.

There is a way to change arrays if the formula evaluates as an error.

=IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$A$3:$Z$4180,2,FA LSE)),IF(ISERROR(VLOOKUP(B3,Master!$AA$3:$AQ$4180, 2,FALSE))))

All you are doing here is saying if VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE)
evaluates as an error, use the same formula in a different array. If the
second array is an error, try the third one, as so on.

Give that a shot. I just did the formula on the fly, so there may be a
missing "(" some where

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"ksean" wrote:

I am using the following formula to get an answer from a single column of a
spreadsheet (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.

Note: I also posted this question in the Applications Errors section by
accident... sorry