Adjusting lookup range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Snara - 01 Oct 2007 17:00 GMT

Hello - I am using a lookup function e.g lookup(a1, b1:c55) - but I would
like to be able to ajust the range of the lookup vector based on values in
other cells.

E.g. I would like to be able to type 3 and 21 into cells M1 and N1 and make
my formula adjust to Lookup(a1, b3:c21).

Does anyone have any ideas?

Thanks so much!

Reply to this Message

Pete_UK - 01 Oct 2007 17:17 GMT

Try this:

=LOOKUP(A1,INDIRECT("B"&M1&":C"&N1))

Hope this helps.

Pete

> Hello - I am using a lookup function e.g lookup(a1, b1:c55) - but I would
> like to be able to ajust the range of the lookup vector based on values in
[quoted text clipped - 6 lines]
>
> Thanks so much!

Reply to this Message

Bob Phillips - 01 Oct 2007 17:20 GMT

=LOOKUP(A1, OFFSET(B3,,,N1-M1+1,2))

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello - I am using a lookup function e.g lookup(a1, b1:c55) - but I would
> like to be able to ajust the range of the lookup vector based on values in
[quoted text clipped - 7 lines]
>
> Thanks so much!

Reply to this Message