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
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

 

[PJFry]

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

 

[ksean]

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]

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

The problem was that there was no 'false' statements for the if(iserrors.

Post back and let me know if that works.
--
Regards,

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

 

[PJFry]

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]

Thank you for all of your hard work it is very much appreciated however I was
still not able to obtain the solution I was looking for and I suspect it was
due to the poor quality of my explanation of my delema therefore I think
that I need to re-explain my delemma.

I have included a pair of sample tables (sheets) that are small examples of
what I am trying to do.

Using the data from the MASTER sheet answer the question marks on the ANSWER
sheet, note that at any given time I may rearrange the ID Numbers in columns
A, D & G of the MASTER sheet so the ANSWER sheet will have to automatically
adjust to my revisions.

MASTER sheet
A B C D
E F G H I
ID No. Last Name First Name ID No. Last Name First Name ID No. Last
Name First Name
1 Calvert Michael 2 Reimer Connor Keating Kyler
Pell Derek Hardick Joshua 4 McKee Drew
10 Pell Trevor 14 Minchau Jennifer McKee Jimmy
McPherson Jessica 11 Horner Cheyenne 9 Norman Trey
6 Quinlan Cody Dewilde Tylen 13 Olson Jessie
Calef Duncan 7 Gray Braden 3 Orchard Aedan
16 Calef Brenden Hagstrom Graham Orchard Garrett
5 Calef Wesley Houle Erin Quaghebeur Justin
Smith Alex 12 Keating Kassandra 8 Reid Andrew

ANSWER sheet
A B C
Last Name First Name
1 ? ?
2 ? ?
3 ? ?
4 ? ?
5 ? ?
6 ? ?
7 ? ?
8 ? ?
9 ? ?
10 ? ?
11 ? ?
12 ? ?
13 ? ?
14 ? ?

I hope that this helps to clarify my delemma and I appreciate any ideas you
might have that will lead me to solving it.

Thanks
ksean

 

[ksean]

I tried your formula again with a fresher mind and it appears to work
"AWESOME!!!!" I just need to be more carefull in how I adapt it to each cell.

If I have any further issues with it I will let you know but for now thank
you very very much for your assistance

Thanks

 

[PJFry]

Very good!
--
Regards,

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