Formula Errors

Prevent Excel VLOOKUP #N/A Errors for a Blank Drop-Down List

Elise WilliamsElise Williams Nov 12, 2026 868 views

Question details

Hide the #N/A result from a VLOOKUP formula when its dependent drop-down cell has not yet been selected.

Prevent Excel VLOOKUP #N/A Errors for a Blank Drop-Down List
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the VLOOKUP result cell

Click the cell that currently contains the VLOOKUP formula, such as B2.

2
Wrap the formula with IFERROR

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.

3
Test the blank drop-down

Clear the selection in the drop-down cell or leave it unselected. The result cell should now remain blank instead of showing #N/A.

Use IFERROR to hide VLOOKUP errors until a value is selected
Blank error result applied: The formula displays the lookup result when available and an empty cell display when VLOOKUP returns an error.
Work with formulas in WPS Spreadsheets

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. 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. 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. 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. 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. 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.
Use IFERROR and VLOOKUP to control blank drop-down results.Create and use drop-down lists with Data Validation tools.Open and edit Microsoft Excel .xlsx and .xls workbooks with formula compatibility.Use a familiar spreadsheet interface for quick formula updates.
microsoft office alternative - wps office

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.