Vlookup to reference offset
 

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:D100,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:D10, second from A2:D11 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.

Vlookup to reference offset
 

On Friday, March 8, 2013 12:27:13 AM UTC-8, Subodh wrote:
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:D100,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:D10, second from A2:D11 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,A1:D10,3,FALSE)
=VLOOKUP(A2,A2:D11,3,FALSE)
=VLOOKUP(A3,A3:D12,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:D10,3,FALSE)
=VLOOKUP($A$1,A2:D11,3,FALSE)
=VLOOKUP($A$1,A3:D12,3,FALSE)
etc...

Regards,
Howard