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@
ExcelForum
Mr.Excel
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@
ExcelForum
Mr.Excel