An easier way to do a complex lookup table?

[ivorykeys87]

I am trying to create a simple solution for finding a commission percentage based on the contract levels of a given agent. I could do a lookup table per insurance carrier, but here's how that would potentially look.

Carrier=Aetna    Products=6      Contract Levels= 10         Plan Types = 20      All offered in 50 states with varying commission amounts per state. This one lookup table would have over 60,000 rows! Now multiply that by all the various insurance carriers and you see the conundrum. There has to be an easier way to look up these values in a table than this. 

For those familiar with Excel, I originally did it in there using an INDEX(MATCH()MATCH()) formula on a pivot table. This greatly reduced the amount of cells needed. But there doesn't seem to be a way to replicate this method on Caspio. 

 

Any idea would be greatly appreciated. 

[Alison]

Hi @ivorykeys87,

If you want to display that information on the report, you can use a search or filter criteria based on the several fields.

Please provide with the test data and desired result for better understanding.