Probability matrix value driven by two "single-select" fields / nested-ifs / multi-variate lookup

[Jerry_Argyriou]

Context: CRM probability assignment to opportunity. Complex value chain, multiple stakeholders resulting in 2 variables (dealtype & stage) required to calc probability. Both variables are single-select fields.

I’m desperately trying to avoid nested-ifs as its a recipe for a maintenance disaster. I tried nesting Swtich() - no luck.

I’ve imported and created this table, and even created separate table with rows [dealtype#-stage#, % ]. Neither seem to work as I cannot select a formula field to lookup off probability table.

Any guidance on how to solve this highly appreciated.

[AlliAlosa]

Hi there, this might help point you in the right direction… though it’s not perfect.

Airtable

Probability Test - Airtable

Explore the "Probability Test" base on Airtable.

The matrix is defined on the [Probability Matrix] table. All of those records are linked to a single record on the [Junction] table. In turn, all of the records on the [Opportunities] table must also be linked to the same record on the [Junction] table to get this to work properly.

The [Junction] table contains a rollup field which puts all possible combinations of {Deal Type} and {Stage} into an array. Then, on the [Opportunities] table, that array is parsed via a MID() formula to find a matching {Deal Type} and {Stage}, and return the associated percentage.

Hope this is helpful to get you started! :slightly_smiling_face:

[Jerry_Argyriou]

Great, thank you!! This seems like a far more elegant solution compared to my 31 line (!!) function of nested-IFs clustered within a SWITCH function’s options. Will try it and revert.
Regardless, you’ve definitely moved my solutions-toolbox forward a couple of notches. Thank you!