Prevent Excel VLOOKUP #N/A Errors for a Blank Drop-Down List
Question details
Hide the #N/A result from a VLOOKUP formula when its dependent drop-down cell has not yet been selected.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses a drop-down list as the lookup value for a VLOOKUP formula in an adjacent cell.
- Observed behavior
- Before a user selects a drop-down value, VLOOKUP returns #N/A instead of leaving the result cell blank.
Confirm that the drop-down selection cell and the first column of the lookup range use matching values. Replace A2 and LookupTable in the example formulas with the actual selection cell and lookup range or named range in your worksheet.
Use IFERROR to hide VLOOKUP errors until a value is selected
Use this option when a blank result is acceptable for both an empty drop-down and a lookup value that is not found.
IFERROR returns an empty text string instead of displaying #N/A whenever the VLOOKUP formula produces an error.
Click the cell that currently contains the VLOOKUP formula, such as B2.
In the formula bar, enter =IFERROR(VLOOKUP(A2,LookupTable,2,FALSE),"") and press Enter. Replace A2 with the drop-down cell, LookupTable with your lookup range, and 2 with the needed return-column number.
Clear the selection in the drop-down cell or leave it unselected. The result cell should now remain blank instead of showing #N/A.

Check the drop-down cell before running VLOOKUP
Use this option when you want a blank result only for an unselected drop-down but want to display a message for a selected value that is missing from the lookup table.
Keep VLOOKUP Results Blank Until a Drop-Down Value Is Selected in WPS Spreadsheets
WPS Spreadsheets supports VLOOKUP, IF, and IFERROR formulas, so you can suppress #N/A results from blank drop-down selections and show lookup results only when a valid choice is available.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the drop-down and VLOOKUP formula.
- 2. Select the formula result cell: Click the cell that displays #N/A, then use the formula bar to view and edit its formula.
- 3. Add IFERROR around VLOOKUP: Enter =IFERROR(VLOOKUP(A2,LookupTable,2,FALSE),"") and press Enter, adjusting the cell reference, range, and return-column number as needed.
- 4. Use a blank-cell check when needed: To show a separate message for missing selected values, use =IF(A2="","",IFERROR(VLOOKUP(A2,LookupTable,2,FALSE),"Not found")).
- 5. Test the drop-down list: Select, clear, and change items in the drop-down to confirm that blank selections no longer show #N/A.

Frequently Asked Questions
Why does VLOOKUP show #N/A when my drop-down list is blank?
VLOOKUP attempts to find the empty value from the drop-down cell in the first column of the lookup range. If no matching blank value exists, it returns #N/A.
What formula leaves a VLOOKUP cell blank when the drop-down has no selection?
Use =IF(A2="","",VLOOKUP(A2,LookupTable,2,FALSE)). This checks A2 first and runs VLOOKUP only after a value is selected.
How can I show Not found instead of #N/A for an invalid selection?
Use =IF(A2="","",IFERROR(VLOOKUP(A2,LookupTable,2,FALSE),"Not found")). A blank A2 stays blank, while other lookup errors display Not found.
Will IFERROR hide errors other than a missing VLOOKUP match?
Yes. IFERROR hides every formula error, including #REF! and #VALUE!. Use the IF blank-cell test alone if you only need to prevent a lookup for an empty drop-down.
Does WPS Spreadsheets support Excel VLOOKUP and IFERROR formulas?
Yes. WPS Spreadsheets supports VLOOKUP, IF, and IFERROR formulas and can open and edit Excel .xlsx and .xls files.




