Ranges with in a LOOKUP

[Elijah]

Hi,

Does anyone know how to use the offset (or vlookup) to capture a range for
use in a LOOKUP function?
My lookup function looks like:

=LOOKUP(A20,C1:I1,Q1:W1)

where:

the C1:I1 is the reference range for a particular customer; and
Q1:W1 is the result range for a particular customer.

I want to be able to reference both these ranges (1 row; 7 columns) base on
a vlookup (or offset) from my list of customer names.

Is there a way to do this?

I have a prepared xls example if anyone has the time to help.

thanks

Elijah

 

[Guest]

Elijah,

I set up my own example to figure this out, and it differs in rows and
columns from yours. Hopefully this example will help.

I created two rows for customers 1 and 2. Thet are: B2:K3 and look like:

2) a b c d e 1 2 3 4 5
3) a b c d e 11 12 13 14 15

Then I created two more rows with the offsets in A6:B7 that look like:

1 0
2 1

This means that customer 1 will be offset by 0 and customer 2 will be offset
by 1.

In rows 2 and 3 the letters are the lookups and the numbers are the results.

I put the letter to be looked up in A10 and the customer number to look for
in A11.

The final result is this formula:

=LOOKUP(A10,OFFSET(B2:F2,VLOOKUP(A11,A6:B7,2),0),OFFSET(G2:K2,VLOOKUP(A11,A6:B7,2),0))

I hope this is helpful.

Art

 

[Elijah]

Thanks Art - that did the trick!..

I just added a reference to the offset next to each customers name.

Elijah