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.

score 2 · Accepted Answer · 2018-09-17 13:39:42Z

2

try,

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

edited Sep 17, 2018 at 13:39

answered Sep 17, 2018 at 13:31

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Over a year ago

Sorry to be a pain; do you know if this can return text values as well as numeric? For large quantities, it needs to return "Speak to Manager".

user4039065 Over a year ago

Maybe =if(qty>999, "speak with manager", aggregate(14, 7, ...

Collectives™ on Stack Overflow

Excel Index-Match on multiple criteria with different match types

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related