Changing values in one cell depending on drop down list values in another cell [Solved]

Report
-
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
-
Hello,
I have created a drop down(pick list) in cell A1. In cell B1 I want it to show a specific number value based on which selection has been chosen in the pick list.
Does anyone know of a formula that works this out??

3 replies

Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
136
Paul, Good morning.

Your question can be solved in several ways.

This will all depend on how many options you have on the list.
If there are few options (3 - 4) you can use the IF function.

But the most structured and professional way to do it is to build a reference table somewhere in the spreadsheet and use, for example, the VLOOKUP function.

Please tell us what your case is.
There are many people here willing to help you.
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Paul,

That would depend on how many values you have.
  • If you have just a few values:

=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,"")))
Where a returns 1, b returns 2 and c returns 3.
  • When you have a whole list of values, you may want to use a separate table for that and use VLOOKUP to pull the right value from that table.

Lets say you put the table in column D and E up to row 10 with headers. Then your formula would look like:
=VLOOKUP(A1,D2:E10,2,0)

Best regards,
Trowa
Trowa,
Superstar, thank you the IF formula worked.
as you can tell, I am very new to excel formulas so your help has been very much appreciated and has reduced my headache.
Thanks again
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
You are very welcome Paul!

If you have any more questions, we are here to help.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!