Excel Index-Match on multiple criteria with different match types

Question

I am trying to write a formula to lookup a value based on two columns, with one being an exact match and one approximate.

I have been looking at https://exceljet.net/formula/index-and-match-with-multiple-criteria, but that only works for both being exact matches.

The project is regarding customer pricing discounts. Each customer is assigned a discount group, with has quantity breaks depending on how many they order.

So for example:

Discount Group  Qty Break   % Discount
A               0           0%
A               50          10%
A               100         20%
B               0           0%
B               100         15%
B               200         25%

So if a customer in group B orders 150 units, I want to lookup B in the first column, and then the value below 150, so 100, and return 15% discount.

Hope that makes sense! Thanks.

Answer 1

try,

=aggregate(14, 7, c:c/((a$1:a$99="B")*(b$1:b$99<=150)), 1)