How to return a specific value based on a combo box

[juliejo]

Hello! I have an activex combo box linked to a list of services. I need to create a formula in an adjacent cell that reads the value in the combo box and fills in the appropriate price.

For example, if "CE" is chosen in the combo box, the adjacent cell would be filled in with 0.022.

Normally, I would use an IF statement to run through all the options and tell the cell what value to return. However, I can't get my formula to recognize the name of my combo box. For example, IF(ComboBox1="CE",0.022) returns a #NAME? error.

What's the secret to referencing a combo box in a formula? And is there a better formula than an IF formula to do this?

Thanks!

[#2 · Accepted solution]

@juliejo 

1. Set the LinkedCell property of the combo box to the address of a cell on the worksheet, for example to the cell under the combo box. In the example below, this is A2. When you select an item from the dropdown list, it will be entered in the linked cell.
2. Create a list with the services in the first column, and the corresponding prices in the second column. You can use the first column as the ListFillRange of the combo box. In the example, it is E2:E6.
3. In the cell where you want the price, enter a VLOOKUP formula (in combination with IFERROR):  =IFERROR(VLOOKUP(A2,$E$2:$F$6,2,FALSE),"")

[#3]

That formula makes no sense to me, but it works, so I don't care! :)

Thanks!

[#4]

@juliejo 

VLOOKUP(A2,$E$2:$F$6,2,FALSE) means:

Take the value of A2, and look for it in the first column of E2:F6, i.e. in E2:E6.

If you find it, return the corresponding value from the 2nd column, i.e. from F2:F6.

FALSE tells Excel to look for an exact match.

If the value of A2 is not found, VLOOKUP will return the error value #N/A.

We wrap VLOOKUP in IFERROR to replace the error value #N/A with the empty string "".