Vlookup formula inside another vlookup formula

[akuku]

Hi,


We have two functions:

1) =VLOOKUP(A4;Sheet2!A3:C21;3;FALSE) - it gives the result: 6

2) =VLOOKUP(B1;Sheet2!A3:B21;2;FALSE) - also gives result: Sheet2!A3:C21


so if both functions are working why in this function:

=VLOOKUP(A4;VLOOKUP(B1;Sheet2!A3:B21;2;FALSE);3;FALSE) which is built from previous two functions, I receive the #VALUE! error?


As you can see 2) function is used to calculate the array for the 1) function.

 

[Kevin Lehrbass]

Hi Akuku. Nice question!

Try this (it works on my Excel)


=VLOOKUP(A4;INDIRECT(VLOOKUP(B1;Sheet2!A3:B21;2;FALSE));2;FALSE)


Cheers,

Kevin

http://www.youtube.com/user/MySpreadsheetLab