Complex lookup

P

pdberger

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

JE McGimpsey

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)
 
P

pdberger

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

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top