Google Sheets — #N/A Error — What It Means & How to Fix It

What Does This Error Mean?

The #N/A error in Google Sheets means a formula looked for something but could not find it. N/A stands for 'Not Available' — the value you asked for does not exist in the range you told Sheets to look in. This is most common with VLOOKUP, HLOOKUP, MATCH, and INDEX formulas. It is not a bug in your spreadsheet — it is Sheets telling you honestly that the search came up empty.

Affected Models

Google Sheets (web browser)
Google Sheets (Android app)
Google Sheets (iPhone app)

Common Causes

A VLOOKUP is searching for a value that does not exist in the lookup column
The search value has extra spaces or different capitalisation compared to the data it is trying to match
The lookup range does not include the column where the search value actually lives
The data being searched is formatted differently — for example, one cell has a number and the lookup value is text that looks like the same number
An EXACT match is required but the value in the sheet has a typo or formatting difference

How to Fix It

Identify which formula is causing the #N/A error. Click the cell showing #N/A and look at the formula bar. You will see a formula like =VLOOKUP(A2, D:F, 2, false). The first argument (A2 in this example) is the value being searched for.

Write down or note what value is being searched for — you will need it in the next step.
Check whether the search value actually exists in the lookup range. Look at the column being searched (D in the example above). Use Ctrl + F to search for the value. If it is not there, that is why you get #N/A — the value genuinely does not exist in your data.

If the value is missing from the lookup column, either add it to the source data or correct the value you are searching for.
Check for hidden spaces. Select the cell containing the lookup value, go to the formula bar, and look carefully for spaces before or after the text. You can also use =TRIM(A2) in an empty cell to strip spaces and see if the clean version matches.

Spaces are invisible and very common when data is copy-pasted from a website or imported from another system.
Check data types. Numbers stored as text look identical to real numbers but will not match in a VLOOKUP. Click the lookup value cell — if the number is left-aligned, it is stored as text. Click the cell, go to Format > Number > Number to convert it to a real number.

In the lookup range, all values must be the same type as the search value. Text '123' and number 123 look the same but are not equal to Sheets.
Wrap the formula with IFERROR to show a friendly message instead of #N/A. Change =VLOOKUP(A2,D:F,2,false) to =IFERROR(VLOOKUP(A2,D:F,2,false), 'Not found'). This replaces the red error with whatever message you choose.

IFERROR is the standard way to handle expected #N/A errors gracefully — for example, when you expect some lookups will simply not find a match.

When to Call a Professional

#N/A is a formula logic issue, not a spreadsheet problem. You can fix it yourself by checking whether the value you are searching for actually exists in the lookup range. If you have a very complex spreadsheet with hundreds of formulas and cannot trace the source, a spreadsheet-savvy colleague or consultant can audit it for you.

Frequently Asked Questions

Is #N/A always a mistake, or can it be expected?

It can definitely be expected. For example, if you have a product lookup and some products have been discontinued, a VLOOKUP for those products will return #N/A because they are not in the active product list anymore. In cases like this, use IFERROR to replace #N/A with something meaningful like 'Discontinued' or 'Not listed'.

My VLOOKUP worked fine yesterday but now shows #N/A everywhere. What changed?

The most common reason is that the source data changed. Someone may have edited, moved, or deleted rows in the lookup range. Check whether the lookup column has been moved — if you had VLOOKUP looking in column D and that column was shifted, the range is now wrong. Also check if any filter is hiding rows in the lookup range.

Can I use a formula that returns a blank instead of #N/A?

Yes — use IFERROR with an empty string: =IFERROR(VLOOKUP(A2,D:F,2,false), ""). The two double quotes with nothing inside them tell Sheets to show an empty cell instead of an error. This is useful when you want the spreadsheet to look clean without any visible error text.