VLOOKUP If Blank Return Blank? How to Fix

Yes, you can make VLOOKUP return a blank cell when the lookup value is blank, but the standard VLOOKUP formula doesn’t do this on its own—it returns a 0 or #N/A error. You need to wrap VLOOKUP in an IF function to check for a blank lookup cell first, like this: =IF(A2="","",VLOOKUP(A2,range,col,FALSE)). This article explains exactly how that works, why VLOOKUP behaves this way, and several related fixes you’ll likely need.

Why VLOOKUP Returns 0 or #N/A for Blank Cells

VLOOKUP’s job is to find a value in the first column of a table and return a value from another column. When you give it a blank cell as the lookup value, two things can happen:

• Blank cell treated as 0: If you use TRUE (approximate match) or omit the fourth argument, Excel treats a blank cell as 0. It then looks for 0 in the first column. If 0 isn’t there, you get #N/A. If 0 is there, you get the corresponding value—which is almost never what you want.
• Blank cell treated as text: If you use FALSE (exact match), Excel looks for an empty string. Unless your table has a blank cell in the first column, you’ll get #N/A.

Either way, a blank lookup cell produces a result that looks broken. The fix is to intercept the blank before VLOOKUP even runs.

The Basic IF + VLOOKUP Formula

The most common solution is:

=IF(A2="","",VLOOKUP(A2,Sheet2!$A$1:$B$100,2,FALSE))

Here’s what each part does:

• IF(A2="","",...) – If cell A2 is blank, return a blank string (which displays as an empty cell).
• VLOOKUP(...) – If A2 is not blank, run the normal lookup.
• FALSE – Forces an exact match, which is almost always what you want for lookups.

This works in Excel and Google Sheets identically. You can also use IF(A2="","",...) with other lookup functions like INDEX/MATCH or XLOOKUP.

What About Returning Blank When the Lookup Finds Nothing?

That’s a different problem. If the lookup value exists but isn’t found in the table, VLOOKUP returns #N/A. To return a blank instead of the error, wrap the whole thing in IFERROR:

=IFERROR(VLOOKUP(A2,range,col,FALSE),"")

This returns a blank when the value isn’t found. But be careful: IFERROR also hides other errors, like a typo in the range or a column number that’s too large. If you want to hide only #N/A, use IFNA instead (available in Excel 2013+ and Google Sheets):

=IFNA(VLOOKUP(A2,range,col,FALSE),"")

Combining Both: Blank Lookup Cell OR No Match

Often you need both protections. The cleanest way is to nest them:

=IF(A2="","",IFNA(VLOOKUP(A2,range,col,FALSE),""))

This returns a blank if A2 is blank, and also returns a blank if A2 isn’t found. If A2 has a value that exists, you get the result. If you also want to catch other errors (like a wrong column number), swap IFNA for IFERROR.

Why Not Just Use IF(A2=””,””,VLOOKUP(…)) with TRUE?

You can, but it’s a bad idea. Approximate match (TRUE) requires the first column to be sorted ascending. If it isn’t, you’ll get wrong results, not errors. And a blank cell is treated as 0, which might match a real 0 in your table. Always use FALSE for exact matches unless you’re doing a specific range lookup (like tax brackets or shipping tiers).

Common Mistakes and Gotchas

• Using "" vs. 0: The formula returns a text string that looks blank. If you need a truly empty cell (for formulas that count blanks), you can’t achieve that with a formula—you’d need to use a macro or copy-paste values. For most purposes, "" is fine.
• Trailing spaces: A cell that looks blank may contain a space. A2="" won’t catch it. Use =IF(TRIM(A2)="","",...) to treat spaces as blank.
• Leading/trailing spaces in the table: If the first column has extra spaces, VLOOKUP won’t match. Use TRIM on the lookup value or clean the table.
• Numbers stored as text: If your lookup value is a number but the table has it as text (or vice versa), VLOOKUP fails. Convert one or the other with TEXT or VALUE.
• Whole column references: Using A:A can slow down large sheets. Use a defined range like $A$1:$B$1000.

Alternatives to VLOOKUP

If you’re starting fresh, consider XLOOKUP (Excel 365, Excel 2021, and Google Sheets). It has built-in handling for blank lookup values and errors:

=XLOOKUP(A2,range,return,"","",2)

This returns a blank if A2 is blank, and a blank if no match is found. The 2 at the end forces exact match (use 0 for exact match in Excel; Google Sheets uses 2 for exact match). INDEX/MATCH is another option that’s more flexible but requires the same IF wrapper for blanks.

Frequently Asked Questions

Why does VLOOKUP return 0 when the lookup cell is blank?

Because with approximate match (TRUE), Excel treats a blank cell as 0. If your table has a 0 in the first column, it returns the corresponding value. If not, you get #N/A. Using FALSE avoids this, but you still need the IF wrapper to get a blank.

Can I make VLOOKUP return a blank for both blank lookup and not found?

Yes, use =IF(A2="","",IFNA(VLOOKUP(A2,range,col,FALSE),"")). This is the most robust formula for the common case.

Does this work in Google Sheets too?

Yes, the exact same formulas work in Google Sheets. Google Sheets also supports IFNA and IFERROR.

What if my blank cell has a space in it?

Use =IF(TRIM(A2)="","",VLOOKUP(...)) to ignore spaces. Or clean the data with =TRIM(A2) in a helper column.

Is there a way to return a blank without a formula?

If you don’t want the formula to show anything, you can use conditional formatting to make the font white, but that’s hiding, not blank. To get a truly empty cell, you’d need to copy and paste values or use a macro.

Why does my VLOOKUP return #N/A even when the value exists?

Often it’s because of trailing spaces in the table, numbers stored as text, or using TRUE when the table isn’t sorted. Try TRIM on both sides, or use VALUE to convert text numbers.

Related

• Hardware Support Services: What They Cover
• Unlock All Three Moonlight Peaks Forms
• Arknights Endfield Twitch Drops: Link & Claim Guide