Combine VLOOKUP and IFERROR to Return Blanks Instead of Errors
Question details
The user needs a formula that prevents VLOOKUP from showing #N/A errors or zeros, instead displaying an empty (blank) cell when a lookup value is missing or the matched source cell is empty.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Pulling data from an external workbook or range where some lookup values might not exist, or the corresponding result cells are blank.
- Observed behavior
- A standard VLOOKUP formula returns an #N/A error when the lookup value is missing, and returns a 0 when the matched destination cell is blank or contains a zero.
Ensure you know your lookup value's cell reference, the exact range of your source data, and the column index number you want to retrieve.
Use a Nested IFERROR, IF, and VLOOKUP Formula
This solution nests VLOOKUP inside an IF function to check for zeros, and wraps it all in an IFERROR function to handle missing data errors.
By default, VLOOKUP cannot output a blank string if the source cell is empty; it outputs a 0. Combining IF helps convert that 0 to a blank, while IFERROR suppresses the #N/A error caused by missing lookup values.
Click on the cell where you want the lookup result to be displayed.
Type the formula: =IFERROR(IF(VLOOKUP(A8, LookupRange, 9, FALSE)=0, "", "Y"), "").
Replace 'A8' with your specific lookup value cell, 'LookupRange' with your actual data range, and '9' with your target column index. If you want to return the actual value instead of 'Y', replace '"Y"' with another VLOOKUP(A8, LookupRange, 9, FALSE).
Press Enter to execute the formula, then drag the fill handle down to apply it to the rest of your column.
Easily Manage Complex Formulas in WPS Spreadsheet
WPS Spreadsheet fully supports advanced nested formulas like VLOOKUP, IFERROR, and IF. Its intuitive interface helps you build complex data-cleaning formulas without syntax errors.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your lookup tables.
- 2. Access the Formula tab: Navigate to the 'Formulas' tab on the top ribbon and click 'Insert Function'.
- 3. Build your nested function: Search for IFERROR, and use the formula builder prompts to safely nest your IF and VLOOKUP functions without manually typing every bracket.
Frequently Asked Questions
Why does VLOOKUP return a 0 for blank cells?
By default, spreadsheet software interprets an empty cell in the matched destination as a zero rather than a blank text string. Wrapping the VLOOKUP in an IF statement helps explicitly convert that 0 back into a blank space.
Can I return a custom message instead of a blank cell?
Yes. In the formula =IFERROR(value, ""), replace the empty quotes at the end with your desired text wrapped in quotes, such as =IFERROR(value, "Not Found").
What is the difference between ISNA and IFERROR?
IFERROR catches all types of formula errors (including #N/A, #VALUE!, and #REF!), making it a broader safety net. ISNA specifically targets only the #N/A error. For most VLOOKUP applications, IFERROR is shorter to write and highly effective.
