I want to make a cell equal a value based on the value in a list

[maddjohn1985]

I'm making a template where I want a cell to populate with a value depending on what value has been selected from a list.

Example

Cell A1 has list

Red

Yellow

Orange

Pink

Blue

If Red, Yellow, Orange, Pink are selected I want cell A4 to populate with 1234.

If Blue is selected I want A4 to populate 5678.

It is seemingly more complicated than I initially thought.

Appreciate any help

[#2]

@maddjohn1985 

Setup a table (named TableColorValue above) with the desired VALUE by COLOR. In A4 assuming you run 2021/365:

=IF(ISBLANK(A1), "", XLOOKUP(A1, TableColorValue[COLOR], TableColorValue[VALUE]))

[#3]

@maddjohn1985 

You can try this:

=IF(OR(A1="Red",A1="Yellow",A1="Orange",A1="Pink"),1234,IF(A1="Blue",5678,""))

[#4]

@Martin_Angosto 

Thanks, that's worked a treat.

I've now also come across an issue where I need a cell to populate one of 3 values based on the contents of 2 cells.

So If B18 equals Blue and B20 equals Red then B23 equals 1234 but if B18 equals Blue and B20 equals Yellow them B23 equals 5678, but if B18 equals Pink and B20 equals Yellow then B23 equals 9876

Hopefully that's also possible and again appreciate your help

[#5]

@maddjohn1985 

Glad it initially worked!

For this second purpose, you can use:

=IF(AND(B18 = "Blue", B20 = "Red"), 1234, IF(AND(B18 = "Blue", B20 = "Yellow"), 5678, IF(AND(B18 = "Pink", B20 = "Yellow"), 9876, "")))

Note that in the event that none of the two scenarios/combinations exist, the formulation would just leave cell B23 blank.