Lookup Tables

  • Thread starter Thread starter WM
  • Start date Start date
W

WM

Does anyone know if it is possible to perform a VLookup
with 4 table arrays across 4 different worksheets.

If not does anyone know if it is possible to combine
VLookups with nested if statements. I have a colleague
who instead of combining 4 worksheets to create a large
table wants the information to remain on independent
sheets but still return a lookup value in a new sheet.

Logic tells me no but does anybody know better.

Thank you
 
You cannot use one vlookup but you can use four

=IF(ISNUMBER(MATCH(lookup_value,'Sheet1'!A1,A50,0)),VLOOKUP,lookup_value,'Sh
eet1'!A1,C50,3,0),IF(next and so on

it could look like

=IF(ISNUMBER(MATCH(A1,Sheet1!$A$1:$A$5,0)),VLOOKUP(A1,Sheet1!$A$1:$C$5,3,0),
IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$5,0)),VLOOKUP(A1,Sheet2!$A$1:$C$5,3,0),I
F(ISNUMBER(MATCH(A1,Sheet3!$A$1:$A$5,0)),VLOOKUP(A1,Sheet3!$A$1:$C$5,3,0),IF
(ISNUMBER(MATCH(A1,Sheet4!$A$1:$A$5,0)),VLOOKUP(A1,Sheet4!$A$1:$C$5,3,0),"No
Match"))))

not a beauty, you can also download morefunc from Laurent Longre, he has a
function called THREED that will take arrays from different sheets and
combine them into 2D array so you can use it with one vlookup
 
Back
Top