Forum Discussion
Solved
How to return a specific value based on a combo box
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 examp...
- 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.
- 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.
- 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),"")
- 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.
- 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.
- 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),"")
That formula makes no sense to me, but it works, so I don't care! 🙂
Thanks!
Thanks!
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 "".