MrExcel Publishing
Your One Stop for Excel Tips & Solutions

use of OFFSET in VLOOKUP


Posted by Kristel on January 07, 2002 3:02 AM

i'm trying to use a variable table in VLOOKUP, i wanted to of the OFFSET function but this seems not to work...
WHen i use
=VLOOKUP(C19;OFFSET(A4:K15;0;A22;;8);8)
i always get #N/A
When i use
=SUM(OFFSET(A4:K15;0;A22;;8);8) i get the right answer
Is this a known error in EXCEL, i thougt it should be possible to use OFFSET inside VLOOKUP
CAn anyone help me with this please?


Posted by Aladin Akyurek on January 07, 2002 3:36 AM

Kristel --

Specifying a table by means of OFFSET is a bit hard. But, it's possible as you can see from the following:

In C3:E5 enter:

{1,2,3;
"a1","Paris","Saint Michel";
10,20,30}

=VLOOKUP("a1",OFFSET(C3:E5,-1,0,4,3),2,0)

should give "Paris" as result., so would

=VLOOKUP("a1",OFFSET(C2:E5,0,0,4,3),2,0)

Hope this helps.

Aladin

=========