How do I calculate IF statement to multidrop down list?

CieraB

I can trying to get my sheet to calculate different values based on what is selected in the drop down. Example: If "Partner introduction" is selected populate 10 points in "points earned" column and $1 in the "dollar value earned" column, If "Quote Request" is selected populate 50 points in the "points earned" column and $5 in the "dollar value earned" column. For some reason, I can think of how I type out this formula. PLEASE HELP

Werner Gerstacker

Hi @CieraB - there are two ways to do this that I can think of:

1) Nested IF statements - the fomula in column 'Points Earned' would be something like:

=IF([Action Item]@row = "Partner Introduction", 10, IF([Action Item]@row = "Quote Request", 20, IF([Action Item]@row = "Closed Deal", 30, EtcEtcEtc)))

Same for the 'Dollar Volume Earned' colum.

2) VLOOKUP - you need either a 2nd sheet that holds your dropdown options as well as the 'Points Earned' and the 'Dollar Volume Earned' values for each or you use a separate section way to the right of your sheet.

You then run a formula like

= VLOOKUP([Action Item]@row, [Action Item]:[Dollar Value Earned],2 false)

to retrieve the value for each row.

Werner Gerstacker

Hi @CieraB - there are two ways to do this that I can think of:

1) Nested IF statements - the fomula in column 'Points Earned' would be something like:

=IF([Action Item]@row = "Partner Introduction", 10, IF([Action Item]@row = "Quote Request", 20, IF([Action Item]@row = "Closed Deal", 30, EtcEtcEtc)))

Same for the 'Dollar Volume Earned' colum.

2) VLOOKUP - you need either a 2nd sheet that holds your dropdown options as well as the 'Points Earned' and the 'Dollar Volume Earned' values for each or you use a separate section way to the right of your sheet.

You then run a formula like

= VLOOKUP([Action Item]@row, [Action Item]:[Dollar Value Earned],2 false)

to retrieve the value for each row.

Paul Newcome

With that many different options, I suggest creating a table similar to @Werner Gerstacker's second solution. The only thing I personally would change is to use an INDEX/MATCH instead of a VLOOKUP, but depending on the specifics, that could end up being strictly personal preference instead of making a functional difference.

Either way... I strongly recommend referencing a table instead of a nested IF for this many different options.