Formula to Return cell value based on multiple Max & Min values

Please double check your values from classes A, AAA, and VIP. In some cases, the Min is greater than the Max. There are also instances in which A has a higher price than VIP, which doesn't seem right. If the prices are all meant to be listed from left to right in ascending order, then you should be able to use the formula below in T4:

=INDEX($B$2:$O$2,FLOOR(1+MATCH($S4,$B4:$O4,1),2)-1)

Fill it down and it should work, but again, it requires the prices in B:O to be listed from lowest to highest.

If the values are in ascending order from B:O, then you should be able to use the following formula:

=IF(FLOOR(1+MATCH($S4,$B4:$O4,1),2)-1>8,(S4-I4)/S4,"")

This formula will remain blank if the class is BBB or worse. If the class is better than BBB, it will return the minimum discount that must be given to lower the class to BBB.

Hopefully that helps?

This formula should give you the discount range that would land you in BBB, again assuming that all of the values are corrected to be in ascending order:

=IF(FLOOR(1+MATCH($S4,$B4:$O4,1),2)-1>8,TEXT((S4-I4)/S4,"0.0%")&" - "&text((S4-H4)/S4,"0.0%"),"")

Some of the percentages for the discounts for the lower priced items might look off because the prices in your table appear to be round numbers, but they're actually decimals that have been rounded (for example, cell I7 shows 28, but it's actually 27.5). The discount math should work if you're looking at the actual price in each cell and not just the price being displayed.

It looks more complicated than it is because you have merged cells in B2:O2, so we have to identify the FIRST of the two cells for each range in order to return the class. For instance, H2 returns "BBB", but I2 is blank.

FLOOR(1+MATCH($S4,$B4:$O4,1),2)-1

achieves the match to the class. It matches S4 against the ascending values in B4:O4 and returns the greatest value equal to or beneath S4. We then add one to that value and round down (using FLOOR) to the next lowest even number, then subtract 1 to get the match with the class name. That sounds confusing, but walk through it with two examples and it'l be clearer. If the price of TSH is 100, then the MATCH will return the 5th value between B4 and O4. We then add one (total now 6), round down to the next lowest even (still 6), and subtract one (now 5). INDEX then returns the 5th value in B2:O2, which is "B". If TSH were 104, MATCH would return 6, because it's the 6th value in B4:O4. We then add 1 (now 7), round down to the next lowest even using FLOOR (now 6), then subtract 1 (now 5), and use INDEX to return the 5th value in B2:O2, which is again "B".

INDEX is used in my original formula to return the class. In my second formula for the discount range, we just use the FLOOR part and a ">8" to determine if the class match is greater than BBB, which is the 7th/8th option in B2:O2. If the class is greater than BBB, then (S4-I4)/S4) returns the minimum discount that would get us to the top of the BBB range. TEXT((S4-I4)/S4,"0.0%") transforms that number into a percentage. &" - "& simply adds a dash to the middle of the discount range. TEXT((S4-H4)/S4,"0.0%") returns the discount that would get us to the bottom of the BBB range, also with TEXT to make it appear as a percentage.

All put together, it should get you what you're looking for. To go through an actual example step by step, select a formula cell and click on "Evaluate Formula" on the "Formulas" tab and it'll walk you through the selected formula one step at a time. Hope this helps!

Great, thank you. Good luck!