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