Vlookup to reference offset

[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,A1100,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 A110, second from A211 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.
ANy suggestions please help.
Thanks in advance.

 

[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,A1100,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 A110, second from A211 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.

ANy suggestions please help.

Thanks in advance.

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

=VLOOKUP(A1,A110,3,FALSE)
=VLOOKUP(A2,A211,3,FALSE)
=VLOOKUP(A3,A312,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,A110,3,FALSE)
=VLOOKUP($A$1,A211,3,FALSE)
=VLOOKUP($A$1,A312,3,FALSE)
etc...

Regards,
Howard