RE: Using VLOOKUP to locate an answer in one of multiple columns

Correction:

=IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE)),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,FALSE)),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$4180,2,FALSE)),"Not
Found",VLOOKUP(B3,Master!$AF$3:$AQ$4180,2,FALSE)),VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,FALSE)),VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE))
--
Regards,

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



"ksean" wrote:

I must be missing something the formula I have ended up with is
=IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE))),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,FALSE))),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$4180,2,FALSE))) but it still doesn't work.

Do you have any ideas what I am missing?



"PJFry" wrote:

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,FALSE)),IF(ISERROR(VLOOKUP(B3,Master!$A$3:$Z$4180,2,FALSE)),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
spread*** (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
.