Forum Discussion
I want to make a cell equal a value based on the value in a list
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
4 Replies
You can try this:
=IF(OR(A1="Red",A1="Yellow",A1="Orange",A1="Pink"),1234,IF(A1="Blue",5678,""))
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
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.
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]))
