How to Use INDEX and MATCH or VLOOKUP with a Combo Box in Excel
Question details
Return a value in Sheet1!B8 from Sheet2 based on the bandwidth selected in Sheet1!B13, with the formula displaying blank when the selection is empty or invalid.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is building a worksheet where B13 acts as a combo box for bandwidth values and B8 should display the corresponding return value from Sheet2.
- Observed behavior
- The formula must return the matching value and show blank when B13 is empty or no match exists.
Confirm that Sheet2 contains the bandwidth list in A2:A11 and the corresponding return values in B2:B11. Also make sure B13 will be used as the dropdown cell for the bandwidth selection.
Create the dropdown with Data Validation and return the value using VLOOKUP
Use this method to create a stable combo box cell in Sheet1!B13 and automatically return the matching Sheet2 value in Sheet1!B8.
This solution uses a Data Validation list instead of an ActiveX Combo Box, so the formula works reliably in WPS Office and Excel without VBA.
Select Sheet1!B13, click the Data tab, and choose Validation in the Data Tools group.
In the Data Validation dialog, set Allow to List, then set Source to =Sheet2!$A$2:$A$11 and click OK.
Select Sheet1!B8, type =IFERROR(VLOOKUP(B13,Sheet2!$A$2:$B$11,2,FALSE),"") and press Enter.
Use INDEX and MATCH as an alternative lookup formula
Use this approach when you prefer INDEX MATCH or need a lookup formula that is easy to adjust.
Return Combo Box Values Instantly in WPS Office Spreadsheet
Use WPS Office Spreadsheet to create a Data Validation dropdown in B13 and return the matching Sheet2 value in B8 with a clean IFERROR VLOOKUP or INDEX MATCH formula. It handles empty or invalid selections without extra VBA.
- 1. Open WPS Office Spreadsheet: Launch WPS Office, open the workbook containing Sheet1 and Sheet2, or create a new spreadsheet.
- 2. Create the bandwidth dropdown in B13: Click Sheet1!B13, go to the Data tab, choose Validation, set Allow to List, and enter =Sheet2!$A$2:$A$11 as the source.
- 3. Enter the lookup formula in B8: Select Sheet1!B8 and type =IFERROR(VLOOKUP(B13,Sheet2!$A$2:$B$11,2,FALSE),"").
- 4. Test the selection: Choose a bandwidth value in B13 and verify B8 updates; clear B13 or enter an invalid value to confirm B8 stays blank.
Frequently Asked Questions
Why does my VLOOKUP show #N/A when B13 is blank?
Because VLOOKUP cannot find an empty cell in Sheet2. Wrap the formula with IFERROR like =IFERROR(VLOOKUP(B13,Sheet2!$A$2:$B$11,2,FALSE),"") to return blank instead.
Can I use an ActiveX Combo Box instead of Data Validation?
Yes, but Data Validation is simpler and more reliable. If you use a Combo Box, set its linked cell to B13 and ensure the output cell formula references B13.
How do I make the dropdown list come from Sheet2 automatically?
Select B13, click Data > Validation, choose List, and set Source to =Sheet2!$A$2:$A$11. New values in that range will appear in the dropdown.
Does INDEX MATCH work with a dropdown in B13?
Yes. Use =IFERROR(INDEX(Sheet2!$B$2:$B$11,MATCH(B13,Sheet2!$A$2:$A$11,0)),"") to return the matching value from column B.
Why is B8 blank even though B13 has a value?
The value in B13 must exactly match a value in Sheet2!A2:A11. Check for extra spaces, case differences, or hidden characters, or confirm the Source range covers the selected value.
