Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Ranges with in a LOOKUP
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) whe 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 |
#2
|
|||
|
|||
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 a 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),O FFSET(G2:K2,VLOOKUP(A11,A6:B7,2),0)) I hope this is helpful. Art |
#3
|
|||
|
|||
Thanks Art - that did the trick!..
I just added a reference to the offset next to each customers name. Elijah "Art" wrote in message ... 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 a 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),O FFSET(G2:K2,VLOOKUP(A11,A6:B7,2),0)) I hope this is helpful. Art |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with graph ranges | Charts and Charting in Excel | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |