Multiselect Dropdown Formula for multiple combinations

kzel

If multiple selections are an option from a drop down column, is there a simple way to write a formula that will display a different result, depending on the combination of item(s) selected? I was able to get the formula to display the result for one combination, but how do I nest each of these individual formulas into one monstrously long formula to include all combinations? So it's really 2 questions:

1. How do I nest these together?
2. Is there some other/more efficient way to write these formulas in the first place?

This formula encompasses all of the single selections:

=IF([Use Type]@row = "Retail", "424", IF([Use Type]@row = "Low Rise Office", "200", IF([Use Type]@row = "Hotel/Motel", "1152", IF([Use Type]@row = "Light Manufacturing", "829", IF([Use Type]@row = "Warehouse", "1174", IF([Use Type]@row = "Retail/Office Mix", "312", IF([Use Type]@row = "Light Man/Warehouse Mix", "1174", IF([Use Type]@row = "Light Man/WH/Office Mix", "849", IF([Use Type]@row = "LM/WH/Office/Retail Mix", "743")))))))))

And here are each of the individual combination formulas:

=IF(AND(HAS([Use Type]@row , "Retail"), HAS([Use Type]@row , "Low Rise Office")), "312")

=IF(AND(HAS([Use Type]@row , "Light Manufacturing"), HAS([Use Type]@row , "Warehouse")), "1174")

=IF(AND(HAS([Use Type]@row , "Light Manufacturing"), HAS([Use Type]@row , "Warehouse"), HAS([Use Type]@row , "Low Rise Office")), "849")

=IF(AND(HAS([Use Type]@row , "Light Manufacturing"), HAS([Use Type]@row , "Warehouse"), HAS([Use Type]@row , "Low Rise Office"), HAS([Use Type]@row , "Retail")), "743")

This just feels like there has got to be a better way to do this - HELP!

TVang

@kzel, with five land use selections, there are 31 possible combinations. A formula to account for all possible combinations will look like spaghetti.

One alternative is to put your use types and codes in a sheet. Then use cross-sheet referencing to perform INDEX(MATCH()) from your main sheet for the code.