How to Make VLOOKUP Return Blank for Missing Values in Excel
Question details
The user needs a VLOOKUP formula to return a blank cell when a lookup item is missing from the reference data range, rather than showing an error or approximate match.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up data in a table where some lookup values might not exist in the reference array, requiring a clean presentation without error indicators.
- Observed behavior
- The VLOOKUP formula returns an #N/A error or an incorrect value because the fourth argument is omitted, defaulting the function to an approximate match.
Ensure your lookup data does not contain hidden leading or trailing spaces, and identify the exact cell ranges of your reference table before updating your VLOOKUP formula.
Use Exact Match and IFERROR to Return a Blank Cell
Wrap your VLOOKUP formula in an IFERROR function and set the fourth VLOOKUP argument to 0 to ensure exact matching while keeping missing values blank.
By default, omitting the fourth argument in VLOOKUP results in an approximate match, which can yield incorrect data. Setting it to 0 or FALSE forces an exact match. Wrapping the entire formula in the IFERROR function catches the #N/A error when a value is not found and replaces it with a blank string ("").
Click on the cell where you want the VLOOKUP result to appear.
Type your VLOOKUP formula and ensure the fourth argument is set to 0 to force an exact match (e.g., =VLOOKUP(B2, G1:H17, 2, 0)).
Modify the formula to include IFERROR at the beginning, specifying a blank string "" for the error condition: =IFERROR(VLOOKUP(B2, G1:H17, 2, 0), "").
If you also want to avoid errors when the lookup cell itself is empty, add an IF statement at the beginning: =IF(B2="","",IFERROR(VLOOKUP(B2,G1:H17,2,0),"")). Press Enter to apply.
Use WPS Spreadsheet to Handle VLOOKUP and Complex Formulas
WPS Office offers a fully-featured Spreadsheet program that natively supports VLOOKUP, IFERROR, and hundreds of other advanced functions. You can easily manage large datasets, apply exact matches, and handle missing data seamlessly.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsx file or create a new spreadsheet.
- 2. Insert Formula: Click on the cell where you need the data and click the 'fx' button next to the formula bar.
- 3. Select IFERROR and VLOOKUP: Search for IFERROR and VLOOKUP, then follow the prompts to select your lookup value, table array, column index, and set range_lookup to FALSE or 0.
- 4. Apply and Drag: Press Enter to apply the formula, then double-click or drag the fill handle down to apply it to the rest of the column.
Frequently Asked Questions
Why does my VLOOKUP return #N/A instead of blank?
VLOOKUP returns an #N/A error when it cannot find an exact match for your lookup value. To hide this error and show a blank cell instead, wrap your VLOOKUP formula with the IFERROR function, like =IFERROR(VLOOKUP(...), "").
What happens if I omit the fourth argument in VLOOKUP?
If you omit the fourth argument (range_lookup), VLOOKUP defaults to TRUE, meaning it will look for an approximate match. This can cause it to return an incorrect value if the exact lookup item is missing. Always use 0 or FALSE to enforce an exact match.
How do I make the formula ignore completely empty lookup cells?
You can use an IF statement to check if the lookup cell is blank before running VLOOKUP. Use the structure =IF(A2="", "", IFERROR(VLOOKUP(...), "")) to keep the destination cell entirely blank when the source cell contains no data.
