How to create an answer based on the user clicking on an option in a drop-down list?

[livinlavidaloca]

I want to create a drop down list where they can select from lots of options, then once they select an option, the cell beside it will provide a unique value of my choice.

For example, in the below table, I'd like to make it so that I call the cell 'Maintenance' then when you click the cell it will list all of the below options, then depending on which option you click on it will provide you with the cost code as seen on the right.

Is it possible?

Thank you in advance!

Maint Materials: Services (Rec VAT)	5620-4500
Maint: Externals (Rec VAT)	5620-4600
Maint Materials: Externals (Rec VAT)	5620-4700
Maint: Landscaping (Rec VAT)	5620-4800
Maint Materials: Landscaping (Rec VAT)	5620-4900
Maint: Pests (Rec VAT)	5620-5000
Maint: Out of Hours (Rec VAT)	5620-5100

 

[Sunshine8790]

If I'm understanding your question correctly, you could do something like this:

Compare and highlight.xlsx
	A	B
1	Service	Code
2	Maint Materials: Services (Rec VAT)	5620-4500
3	Maint: Externals (Rec VAT)	5620-4600
4	Maint Materials: Externals (Rec VAT)	5620-4700
5	Maint: Landscaping (Rec VAT)	5620-4800
6	Maint Materials: Landscaping (Rec VAT)	5620-4900
7	Maint: Pests (Rec VAT)	5620-5000
8	Maint: Out of Hours (Rec VAT)	5620-5100
9	Select One
10	Select One
11	Select One
12	Select One
13	Select One
14	Select One
15	Select One
16	Select One
17	Select One
18	Select One
19	Select One
20	Select One
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	=IFS(A2="Maint Materials: Services (Rec VAT)","5620-4500",A2="Maint: Externals (Rec VAT)","5620-4600",A2="Maint Materials: Externals (Rec VAT)","5620-4700",A2="Maint: Landscaping (Rec VAT)","5620-4800",A2="Maint Materials: Landscaping (Rec VAT)","5620-4900",A2="Maint: Pests (Rec VAT)","5620-5000",A2="Maint: Out of Hours (Rec VAT)","5620-5100",A2="Select One","")

Cells with Data Validation
Cell	Allow	Criteria
A2:A20	List	Select One,Maint Materials: Services (Rec VAT),Maint: Externals (Rec VAT),Maint Materials: Externals (Rec VAT),Maint: Landscaping (Rec VAT),Maint Materials: Landscaping (Rec VAT),Maint: Pests (Rec VAT),Maint: Out of Hours (Rec VAT)

 

[Roncondor]

1. SWITCH would be preferable to IFS
2. Even so, this answer is limited because you have the data hardcoded in the formula so does not allow you to add other options

How about just creating a table using MATCH to find the row and then INDEX to find the desired value