Formula Errors

How to Fix Excel IF and VLOOKUP Formula Returning Blanks

John Wilson Sep 27, 2026 869 views

Question details

The user needs to correct a formula where nesting VLOOKUP inside an IF function returns unexpected blank results for certain lookup values.

How to Fix Excel IF and VLOOKUP Formula Returning Blank Results

Product: Excel
Device & OS: not provided
Scenario: Looking up data across sheets using a combined IF and VLOOKUP formula where values start with specific numbers.
Observed behavior: The formula unexpectedly returns blank values for some data rows (such as numbers below 7) because the lookup range is unsorted and the formula relies on an approximate match.

Before you start

Before modifying your formulas, ensure your source data doesn't contain hidden trailing spaces and that the lookup columns share the exact same data format (e.g., both Text or both Number).

Solution 1Recommended

Use FALSE for an Exact Match in VLOOKUP

Switching the match type to FALSE ensures Excel finds the precise value regardless of how your source data is sorted.

When VLOOKUP uses TRUE (or omits the fourth argument), it defaults to an approximate match. This causes unexpected blanks or incorrect results if the lookup data is not perfectly sorted in ascending order. Using FALSE forces an exact match.

Select the problematic cell

Click on the cell containing your formula and press F2, or click inside the formula bar to edit.

Update the VLOOKUP argument

Locate the VLOOKUP portion of your formula and change the final argument to FALSE. For example: =VLOOKUP(C2, 'Sheet2'!A1:A12, 1, FALSE).

Wrap with IFERROR

To gracefully handle #N/A errors when an exact match isn't found, wrap your formula in IFERROR like this: =IFERROR(IF(VLOOKUP(C2,'Sheet2'!A1:A12,1,FALSE)=C2,"Yes","No"),"No").

Apply the formula

Press Enter, then drag the fill handle down to apply this updated formula to the rest of the column.

Data Accuracy: Using exact match (FALSE) is highly recommended for text lookups, IDs, and unsorted datasets to prevent silent formula errors.

Solution 2

Sort the Lookup Range (For Approximate Matches)

If your scenario strictly requires an approximate match (using TRUE), you must sort your lookup array in ascending order.

WPS Spreadsheet Formula Solution

Write and Troubleshoot Complex Formulas with WPS Office

WPS Spreadsheet makes it simple to write, nest, and debug complex logical formulas. Its intuitive interface helps you quickly identify missing arguments or incorrect match types.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the broken formulas.
2. Activate formula edit mode: Double-click the cell returning a blank result to view the formula.
3. Edit the match type: Change the VLOOKUP match type to FALSE for an exact match, and press Enter to instantly calculate the correct output.

Fully compatible with all Microsoft Excel formulas, including VLOOKUP, XLOOKUP, and IF statements.Built-in error checking highlights formula inconsistencies immediately.Intelligent autocomplete reduces syntax errors and missing parentheses.Completely free and lightweight, offering advanced data analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP return a blank instead of an error message?

VLOOKUP normally returns an #N/A error when a value is not found. However, if it returns a blank cell, the formula is likely wrapped in an IFERROR or IF function instructing it to output a blank ("") on failure, or the source cell it points to is completely empty.

What is the difference between TRUE and FALSE in the VLOOKUP formula?

The fourth argument dictates the match type. FALSE requires an exact match and searches the entire column accurately regardless of sorting. TRUE searches for an approximate match and requires the lookup column to be sorted in ascending order.

Can I use XLOOKUP instead to avoid this sorting issue?

Yes. In modern versions of Excel and WPS Spreadsheet, XLOOKUP defaults to an exact match. You do not need to specify FALSE, and your data does not need to be sorted for it to return accurate results.

Excel & Data Problems