# Vlookup to reference offset

S

#### Subodh

Hii All,
I want to use vlookup in such a way that it includes only a
part of the table - not the whole table.
For eg. Vlookup (A1,A1 100,3,FALSE) works fine.
But, i don't want to use it to lookup for all values of the
table.
So, it should only reference to first 10 rows only.
ie if i copy the formula down on different rows, first row should
include from A1 10, second from A2 11 and so on...
THe way I see the solution is by (A1,OFFSET(A1,D100,1,1,5),3,100) but
this generates and error.

L

#### lhkittle

Hii All,

I want to use vlookup in such a way that it includes only a

part of the table - not the whole table.

For eg. Vlookup (A1,A1 100,3,FALSE) works fine.

But, i don't want to use it to lookup for all values of the

table.

So, it should only reference to first 10 rows only.

ie if i copy the formula down on different rows, first row should

include from A1 10, second from A2 11 and so on...

THe way I see the solution is by (A1,OFFSET(A1,D100,1,1,5),3,100) but

this generates and error.

Try this in the first row and pull down. Looks like it will give you what you want.

=VLOOKUP(A1,A1 10,3,FALSE)
=VLOOKUP(A2,A2 11,3,FALSE)
=VLOOKUP(A3,A3 12,3,FALSE)
etc...

If you want to always have A1 as the lookup value then make this small change to \$A\$1 in the first formula. Then pull down.

=VLOOKUP(\$A\$1,A1 10,3,FALSE)
=VLOOKUP(\$A\$1,A2 11,3,FALSE)
=VLOOKUP(\$A\$1,A3 12,3,FALSE)
etc...

Regards,
Howard