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: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.
 
Ad

Advertisements

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: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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top