Adding results from dropdown list

I have the following formula running which enters a number based on the selection from a dropdown list in cell C24, which works fine:
=IF(C24="",0,IF(C24="Green",115,IF(C24="Red",135,IF(C24="Blue",160,IF(C24="Yellow",180,IF(C24="Orange",120))))))

However I would also like to subject this formula to the results of a second dropdown list, which will include a factor to be applied to each of the colour options, which is based on location. I have a list of about 6 locations, with factors ranging from 0.8 to 1.2. So for instance, if I was to select Green and the location with a factor of 0.8, it would multiply 115 x 0.8. Or if I was to select Yellow and the location with a factor of 1.2, it would multiply 180 x 1.2.

The second dropdown list does not include the multiplication factor, just the location name. The location factors are situated in the column next to the source for the dropdown list.

Any ideas on how I could pull this all together in one formula please?

First of all, you can reduce the length of your existing formula by setting up a small table somewhere and using the VLOOKUP function. The table would look like this:

Green ……. 115
Red …..…...135
Blue ……... 160
Yellow …… 180
Orange …. 120

and suppose you put that in cells X1:Y5, then you formula above would become:

=IFERROR(VLOOKUP(C24,$X$1:$Y$5,2,0),0)

You might already have the colours listed somewhere to drive your drop-down, so you could make that the basis of your table and change the second parameter to suit.

So, extending this further, you could have a list of locations (which you might have for your second drop-down), and against each of them you could have the factor that applies. You can use another VLOOKUP function to retrieve the appropriate factor. To combine them you would just need to do this:

=IFERROR(VLOOKUP(C24,$X$1:$Y$5,2,0) * VLOOKUP(cell2,table2,2,0),0)

where cell2 is the cell for your second drop-down and table2 is the range where your second table is located (as you didn't give details of these).

Hope this helps.

Pete

Fantastic, that's solved it! Thanks very much Pete_UK.

Glad to help - thanks for the rep.

Pete