Look up value with multiple criteria

Question

Category    QTY    $ Disc /unit
A           2   
B           4   
C           2   
A           7   
A           7   
A           16  
B           7   
C           5   
A           1   
C           13  
A           6   
C           9   
B           3   
A           4   
C           3   


Category  Min QTY  Max Qty  $ Disc /unit
A         1        5        $1 
A         6        10       $2 
A         11       11 +     $3 
B         1        5        $2 
B         6        10       $3 
B         11       11 +     $4 
C         1        5        $3 
C         6        10       $4 
C         11       11 +     $5 

I need to get Disc/unit from the table with two criteria

1. Category can be A, B, or C
2. QTY can be between / equal to min. QTy and max qty ranges.

Answer 1

This ain't pretty but it works, IF and that is a big IF:

• Your data is sorted ascending by column G and then column H
• you don't need column I but you can keep it for decoration purposes

The formula in cell D2 and copied down is

=INDEX(INDEX($J$2:$J$10,MATCH(A2,$G$2:$G$10,0)):INDEX($J$2:$J$10,MATCH(A2,$G$2:$G$10,1)),MATCH(B2,INDEX($H$2:$H$10,MATCH(A2,$G$2:$G$10,0)):INDEX($H$2:$H$10,MATCH(A2,$G$2:$G$10,1)),1))

Do not apply that formula to whole columns unless you like to stare at the "Calculating -- 2%" message for minutes on end.