Lookup Tables

[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

 

[Peo Sjoblom]

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

 

[WM]

Wow!! I will go away and try this

Thank you for your help.

WM