IF statements with nested INDEX MATCH

Hello. I am trying to write one Excel formula that can accomplish the following:
Review a Discount Sheet to determine whether a Class Name should be purchased based on its current discount compared to my Target Discount for that Class Name. The majority of the time, weekly discount changes occur in 5-10% increments, but occasionally they will occur in increments of 20% or more. If a discount percentage increases by 20% or more and surpasses my Target Discount, the current method I am using to automate this process does not work.

Example:
Last week’s discount in cell G2 is 25%, and this week’s discount in cell H2 is 40%. The Target Discount for that class is 30%, so under my current system I would not request that Class Name for purchasing because the current discount does not exactly equal my Target Discount, even though the previous week’s discount did not exceed my Target Discount and now I should purchase that Class Name because the discount has gone from not meeting to exceeding my Target Discount.

INDEX MATCH used to retrieve current discount:
=INDEX('Discount Sheet'!$H$2:$H$4910,MATCH(A3,'Discount Sheet'!$F$2:$F$4910,0))

Validity Test in Cell F2 to compare Current Discount to Target Discount:
=E2=C2

NOTE: The above validity test could include a >= to capture Current Discounts that have exceeded the Target Discount; however, over time all Current Discounts will exceed the Target Discount. It is only when last week’s discount did not exceed the Target Discount and now the Current Discount does exceed the Target Discount should a class be purchased.

The final result of this formula should be “TRUE” or “FALSE” without having to cut and paste any data for additional validity comparisons.

Sorry for the detailed post. Any help would be appreciated. Thank you.

Hello,

Thanks for the reply. That formula is similar to what I am doing now with two formulas. Basically this,the only time I want a class name to show up as "Yes" or "True" for purchasing is when last week's discount was below my target discount and now has gone above my target discount. In all future instances where the current discount is above my target discount I DO NOT want that class name to be slated for purchasing, since it already should have been purchased in previous weeks.

Does that make since?

That is absolutely brilliant. Looks like the AND function was the big missing link. Nice to know you can join two index matches to make a complex matching criteria. I have used INDEX MATCH when matching multiple basic criteria with the addition of "&" with multiple cells to match, but never this complex. Outstanding work! I added to your reputation and marked this thread as solved.

Thanks so much.