Complex lookup
Good morning --
I'm trying to design a lookup table to work like this: Type Apples Value 35 Value Earnings: Range 1 Range 2 Range 3 Range 1 Range 2 Range 3 Apples 10 25 50 $50 $75 $100 Oranges 25 50 75 $100 $125 $150 Lemons 30 60 80 $75 $150 $200 The value returned would be $75 (Earnings, Range 2, Apples). Thanks in advance, and happy holidays. |
Complex lookup
One way:
name your table, say, "table" (from "Apples" to $150, 3 rows by 7 columns). Then: =INDEX(table, MATCH(Type, INDEX(table,,1), FALSE), MATCH(Value, OFFSET(table, MATCH(Type, INDEX(table,,1), FALSE)-1,1,1,3), TRUE)+4) In article , pdberger wrote: Good morning -- I'm trying to design a lookup table to work like this: Type Apples Value 35 Value Earnings: Range 1 Range 2 Range 3 Range 1 Range 2 Range 3 Apples 10 25 50 $50 $75 $100 Oranges 25 50 75 $100 $125 $150 Lemons 30 60 80 $75 $150 $200 The value returned would be $75 (Earnings, Range 2, Apples). Thanks in advance, and happy holidays. |
Complex lookup
I assume these are the lookup criteria:
Type Apples Value 35 Can you explain the logic for your desired result? The value returned would be $75 (Earnings, Range 2, Apples). -- Biff Microsoft Excel MVP "pdberger" wrote in message ... Good morning -- I'm trying to design a lookup table to work like this: Type Apples Value 35 Value Earnings: Range 1 Range 2 Range 3 Range 1 Range 2 Range 3 Apples 10 25 50 $50 $75 $100 Oranges 25 50 75 $100 $125 $150 Lemons 30 60 80 $75 $150 $200 The value returned would be $75 (Earnings, Range 2, Apples). Thanks in advance, and happy holidays. |
Complex lookup
I apologize for not responding -- for several days, I was unable to see
anything on this board after 12/23. You're right -- those are the lookup criteria. The formula would find the 'Apples' row. Then, because 35 is greater than 25 but not greater than 50, it would qualify as a 'range 2' result, and find the corresponding 'range 2' figure under the 'earnings' section, which would be $75. Thanks in advance. "T. Valko" wrote: I assume these are the lookup criteria: Type Apples Value 35 Can you explain the logic for your desired result? The value returned would be $75 (Earnings, Range 2, Apples). -- Biff Microsoft Excel MVP "pdberger" wrote in message ... Good morning -- I'm trying to design a lookup table to work like this: Type Apples Value 35 Value Earnings: Range 1 Range 2 Range 3 Range 1 Range 2 Range 3 Apples 10 25 50 $50 $75 $100 Oranges 25 50 75 $100 $125 $150 Lemons 30 60 80 $75 $150 $200 The value returned would be $75 (Earnings, Range 2, Apples). Thanks in advance, and happy holidays. |
Complex lookup
OK, try this:
Table data in the range A3:G5 Lookup values: A10 = apples A11 = 35 =INDEX(E3:G5,MATCH(A10,A3:A5,0),MATCH(A11,INDEX(B3 :D5,MATCH(A10,A3:A5,0),))) If the numeric lookup value is less than the smallest "value" for that item in the table the result will be #N/A. -- Biff Microsoft Excel MVP "pdberger" wrote in message ... I apologize for not responding -- for several days, I was unable to see anything on this board after 12/23. You're right -- those are the lookup criteria. The formula would find the 'Apples' row. Then, because 35 is greater than 25 but not greater than 50, it would qualify as a 'range 2' result, and find the corresponding 'range 2' figure under the 'earnings' section, which would be $75. Thanks in advance. "T. Valko" wrote: I assume these are the lookup criteria: Type Apples Value 35 Can you explain the logic for your desired result? The value returned would be $75 (Earnings, Range 2, Apples). -- Biff Microsoft Excel MVP "pdberger" wrote in message ... Good morning -- I'm trying to design a lookup table to work like this: Type Apples Value 35 Value Earnings: Range 1 Range 2 Range 3 Range 1 Range 2 Range 3 Apples 10 25 50 $50 $75 $100 Oranges 25 50 75 $100 $125 $150 Lemons 30 60 80 $75 $150 $200 The value returned would be $75 (Earnings, Range 2, Apples). Thanks in advance, and happy holidays. |
All times are GMT +1. The time now is 12:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com