VLOOKUP

[Dave]

Is it possible for VLOOKUP to work with a 3D range? - say Sheet1!A2:Sheet5!B100
I can't get it to work, but it may just be a matter of my syntax.
Or is there another way of looking for something in multiple lists?
Regards - Dave.

 

[Son of a Sea Cook]

Is it possible for VLOOKUP to work with a 3D range? - say Sheet1!A2:Sheet5!B100
I can't get it to work, but it may just be a matter of my syntax.
Or is there another way of looking for something in multiple lists?
Regards - Dave.

You can nest the vlookups in an if loop.

If can handle up to 30 nestings.

 

[Dave]

Hi SoaSC,
So is that a "No" to 3D tables in VLookup?
And when you say nesting if statements, do you mean
If(NOT(ISERR(VLOOKUP-For-Sheet1),VLOOKUP-For-Sheet1,IF(NOT(ISERR(VLOOKUP-For-Sheet2),VLOOKUP-For-Sheet2,IF(NOT(ISSERR( etc?
It's really cumbersome. Are you sure there's not a nice 3D way?
Dave.

 

[Jacob Skaria]

Hi Dave

If the sheets are named Sheet1, Sheet2, Sheet3 etc; then try the below
formula which will lookup the value in C1 of current sheet in 5 sheets and
return the matching value...Please note that this is an array formula. Within
the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to
apply this formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}". Try and feedback


=VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:5"))&"!A:A"),C1)>0,0)&"!A:B"),2,0)

If this post helps click Yes

 

[Dave]

Hi Jacob,
I tried this, but couldn't get it to work. It seemed to look at the other
sheets ok, and found the target value, but didn't bring the result from Col 2
adjacent to that target value. Rather, it seemed to be looking in Col 2 of
Sheet1.
Regards - Dave.