Check Qualification based on Multiple Criteria Using a Table with Logic Operators

Hello and thank you in advance for any help or guidance you can provide.

I have a situation where I need to determine whether a business qualifies for a sales program, and if they do qualify, what Tier and Sub-Tier do they fall in. There are several criteria to take into consideration. Rather than write out long nested If/Then/AND/OR statements, I was hoping to set up the qualification requirements in a table for easier management and updating. I have tried creating a table and using Index/Match with multiple criteria, but I was having trouble with the AND/ OR logic within it. I also tried breaking down the logic into chunks based on each qualifier, and then building a reference id that could be used in a look up table, but I had the same issues with the AND/OR Logic.

In my attached sample, the top portion tries to lay out the current qualification logic in plain English. The Output section takes the Input parameters and uses them in a standard nested if statement, to compare to the qualifiers in the table. It works and can return the Tier and Sub-Tier, but I was hoping for a more dynamic solution. I?d like to basically have the logic outlined in a table, and then use the inputs to lookup which Tier and Sub-Tier the Business would fall in to. The last section illustrates what I foresee could be updated qualifications.

Cross Posted@
Mr.Excel
https://chandoo.org/forum/threads/ch...erators.49852/

I don't follow all of the logic.
If the number of new clients is changed to 9 and the new sales is changed to $500,000 sub tier (cell J17) displays 1.
Looking at the tst table it would seem that sub tier should display 2.
If 1 is correct, please explain why.

For ease of adding more variables I would suggest the following as modeled in rows 26:34 on Sheet2.
1. Changing the values in the current business column to minimums (i.e. the last two are >= $0)
2. Change the N/A's in the new client column to zeros
3. Modify the formula in the test columns to: =A$17>=[@[Current Business $]] ; =B$17>=[@[New Clients]] and =C$17>=[@[New Sales $]] respectively
4. Modify the formulas in the lookup columns to: =IF(AND($C31,$G31,$K31),L31,"")
5. Leave the output tier formula but modify the sub-tier formula to read: =IF(E28="Not Eligible","Not Eligible",INDEX(tst_245[SubTier Lookup],MATCH(E28,tst_245[Tier Lookup],0)))
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.