Quick lookup (SUMPRODUCT OR INDEX)

[albardit18]

I am struggling to come up with a formula for the attached file with 2 sets of criteria (zone and weight). The zone is straightforward 1,2,3. However, the weight brackets are such that I would like it to pull the same rate as the table or the next higher one. For example a 0.1 weight would pull the 0.125 rate as there isn't a 0.1 weight rate and the next higher rate is under 0.125 weight.

In cell i2 I tried sumproduct((b6:d28*(g2=b1:d1)*(h2=<a5:a28)) and a similar index/match formula but no luck.

Any ideas?

[benishiryo]

i like your example. it's clear what you want. you can use this array formula:
=INDEX($B$5:$D$28,MATCH(TRUE,($A$5:$A$28>=H2),0),MATCH(G2,$B$3:$D$3,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

you can also rearrange your table by descending order, then this formula will do:
=INDEX($B$5:$D$28,MATCH(H2,$A$5:$A$28,-1),MATCH(G2,$B$3:$D$3,0))

[albardit18]

Thank you! That array formula worked like a charm!