Index/Match Function for Finding Result in between Two Values with Two Criteria

[KyleElliott]

Tried to run a large function but ran into 64 max nested IF statements. I read that i needed to make a table and run an Index/Match type formula. I could figure out how to run with one min/max but couldn't figure out how to run with two.

What I'm looking for is if Value1 (Type1) falls between MinType1 and MaxType1 and Value2 (Type2) falls between MinType2 and MaxType2, it will give you the result (Amount) from that line. Ex Data is provided below the chart.

Chart:
MinType1 MaxType2 MinType2 Max Type2 Amount
100 200 1000 2000 $100
100 200 2001 3000 $200
201 300 1000 2000 $300
201 300 2001 3000 $400

Data:
Type1: 150
Type2: 1500
Result: $100

Might be easier to read in attachment.

[teylyn]

Consider re-arranging your data in a matrix, then you can use an Index/Match combination.

=INDEX($H$4:$I$5,MATCH(A10,$G$4:$G$5,1),MATCH(B10,$H$3:$I$3,1))

2017-06-09_16-48-39.png

[shukla.ankur281190]

Try

C10

=SUM(IF((((A10<$B$2:$B$5)*(B10<$D$2:$D$5))*((A10>$A$2:$A$5)*(B10>$C$2:$C$5))),$E$2:$E$5))

Drag down!!

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