Two way lookup - Text and Number

[thegreencar]

Hi I'm trying to write a formula to solve for the below, where the Item and Quota are given and the multiplier is needed.
Tried doing a sumifs with True lookup back can't get it to work.

Any help is greatly appreciated.

Item Quota <tbody> [TD="class: xl66, width: 76"]Multiplier1
<tbody> [TD="class: xl66"]Item
Thanks,
<tbody> [TD="class: xl67"]

<tbody>

apples

apples

apples

Pears

Pears

Pears

Oranges

Oranges

Oranges

[TD="class: xl66, width: 76"]Multiplier2[/TD]
[TD="class: xl66, width: 76"]Multiplier3[/TD]

[TD="align: right"]20[/TD]
[TD="class: xl67, align: right"]1.05[/TD]
[TD="class: xl67, align: right"]1.10[/TD]
[TD="class: xl67, align: right"]2.00[/TD]

[TD="align: right"]40[/TD]
[TD="class: xl67, align: right"]1.25[/TD]
[TD="class: xl67, align: right"]2.00[/TD]
[TD="class: xl67, align: right"]0.00[/TD]

[TD="align: right"]60[/TD]
[TD="class: xl67, align: right"]1.25[/TD]
[TD="class: xl67, align: right"]2.00[/TD]
[TD="class: xl67, align: right"]0.00[/TD]

[TD="align: right"]10[/TD]
[TD="class: xl67, align: right"]1.10[/TD]
[TD="class: xl67, align: right"]1.20[/TD]
[TD="class: xl67, align: right"]2.00[/TD]

[TD="align: right"]20[/TD]
[TD="class: xl67, align: right"]1.15[/TD]
[TD="class: xl68, align: right"]1.70[/TD]
[TD="class: xl68, align: right"]2.50[/TD]

[TD="align: right"]30[/TD]
[TD="class: xl68, align: right"]1.65[/TD]
[TD="class: xl68, align: right"]2.20[/TD]
[TD="class: xl68, align: right"]3.00[/TD]

[TD="align: right"]50[/TD]
[TD="class: xl67, align: right"]1.05[/TD]
[TD="class: xl67, align: right"]1.10[/TD]
[TD="class: xl67, align: right"]2.00[/TD]

[TD="align: right"]100[/TD]
[TD="class: xl68, align: right"]1.10[/TD]
[TD="class: xl68, align: right"]1.15[/TD]
[TD="class: xl68, align: right"]2.05[/TD]

[TD="align: right"]250[/TD]
[TD="class: xl68, align: right"]1.15[/TD]
[TD="class: xl68, align: right"]1.20[/TD]
[TD="class: xl68, align: right"]2.10[/TD]

</tbody>

[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]

[TD="class: xl67"]Quota[/TD]
[TD="class: xl68, width: 76"] Multiplier1
[/TD]
[TD="class: xl68, width: 76"] Multiplier2 [/TD]
[TD="class: xl68, width: 76"] Multiplier3 [/TD]

[TD="class: xl69"]apples[/TD]
[TD="class: xl70, align: center"]60[/TD]
[TD="class: xl71"] Formula [/TD]
[TD="class: xl71"] Formula [/TD]
[TD="class: xl71"] Formula [/TD]

</tbody>

[/TD]
[TD="align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67, align: right"][/TD]

[TD="align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67, align: right"][/TD]

[TD="align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67, align: right"][/TD]

[TD="align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67, align: right"][/TD]

[TD="align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"][/TD]

[TD="align: right"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"][/TD]

[TD="align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67, align: right"][/TD]

[TD="align: right"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"][/TD]

[TD="align: right"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"][/TD]

[TD="class: xl68"][/TD]
[TD="class: xl68, width: 76"][/TD]
[TD="class: xl68, width: 76"][/TD]

[TD="class: xl69"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[TD="class: xl71"][/TD]
[TD="class: xl71"][/TD]

</tbody>

[/TD]

[TD="class: xl71"][/TD]

[TD="class: xl73"][/TD]
[TD="class: xl74"][/TD]

</tbody>

 

[thegreencar]

Sorry, should have said that the quota figures are the max in that range - so Apples Quota 15, would fall under Quota 20 and Apples Quota 25 would be Quota under Quota 40

 

[AlanY]

try

Excel 2013/2016
	A	B	C	D	E
1	Item	Quota	Multiplier1	Multiplier2	Multiplier3
2	apples	20	1.05	1.1	2
3	apples	40	1.25	2	0
4	apples	60	1.25	2	0
5	Pears	10	1.1	1.2	2
6	Pears	20	1.15	1.7	2.5
7	Pears	30	1.65	2.2	3
8	Oranges	50	1.05	1.1	2
9	Oranges	100	1.1	1.15	2.05
10	Oranges	250	1.15	1.2	2.1
11
12	Item	Quota	Multiplier1	Multiplier2	Multiplier3
13	apples	60	1.25	2	0
Sheet1

Cell Formulas
Range	Formula
C13	{=INDEX($C$2:$E$10,MATCH($A13&$B13,$A$2:$A$10&$B$2:$B$10,0),MATCH(C$12,$C$1:$E$1,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[thegreencar]

Brilliant, thank you so much

 

[AlanY]

that wouldn't work with the condition in post#2, will get back to you later

 

[thegreencar]

Hi, thanks again for the above. Can you help with one further change on this.
I changed this slightly to accommodate different quota values, such as Quota 10 or 11. I did this by changing 0 to 1 in the first Match instance =INDEX($C$2:$E$10,MATCH($A13&$B13,$A$2:$A$10&$B$2:$B$10,1),MATCH(C$12,$C$1:$E$1,0))

If the quota in B13 is 10, this returns N/A in C13, where as I'd like it to return 1.05.

Can you help me fix this?
Thanks,

 

[thegreencar]

Ah, just saw this reply now. Thank you

 

[AlanY]

try this instead, confirm with shift-control-enter

=INDEX($C$2:$E$10,MATCH($A13&SMALL(IF($A$2:$A$10=$A13,IF($B$2:$B$10 > =$B13,$B$2:$B$10)),1),$A$2:$A$10&IF($A$2:$A$10=$A13,IF($B$2:$B$10 > =$B13,$B$2:$B$10)),0),MATCH(C$12,$C$1:$E$1,0))

 

[AlanY]

a simpler one, confirm with shift-control-enter

=INDEX($C$2:$E$10,MATCH(SMALL(IF($A$2:$A$10=$A13,IF($B$2:$B$10>=$B13,$B$2:$B$10)),1),$B$2:$B$10,0),MATCH(C$12,$C$1:$E$1,0))

 

[thegreencar]

Thank you so much for your help!