SKU not in price list
VLOOKUP returns #N/A when the product code in the order row is not in the price table.
What does #N/A mean in Google Sheets?
#N/A means not available: the formula ran but could not find a match. Lookup functions return #N/A when a key is missing from the table. Sometimes that is correct. Often it signals typos, extra spaces, or numbers stored as text.
When to use it
Study #N/A when building lookup columns, price lists, and roster merges so you separate real missing keys from fixable data quality issues.
When to skip it
Do not blanket IFNA every lookup if analysts need to notice missing master data that should be added to the source table.
How it works
-
1
Confirm the lookup value exists in the source column with exact spelling and type.
-
2
Trim spaces with TRIM and normalize case with LOWER on both sides when needed.
-
3
Use FALSE for exact match in VLOOKUP unless you want approximate match on sorted data.
-
4
Wrap with IFNA(value, fallback) when a miss is expected and you want a blank or message.
-
5
Sort lookup tables for approximate VLOOKUP or switch to XLOOKUP with exact match.
-
6
Check for numbers stored as text on one side and real numbers on the other.
Examples in Google Sheets
Extra space in name
TRIM on both columns fixes #N/A caused by trailing spaces in one list.
FILTER with no rows
FILTER can return #N/A when no row matches the condition instead of an empty spill.
Build this without starting from a blank cell
Use a Better Sheets tool for #N/A error, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Assuming #N/A always means broken data when the key truly does not exist yet.
- Using approximate VLOOKUP on unsorted tables, producing wrong matches instead of #N/A.
- Comparing dates stored as text to real date values.
- Hiding all #N/A with IFNA so nobody notices missing catalog items.
- Looking up in the wrong column after someone inserted a column in the master sheet.
Frequently asked questions
- Is #N/A an error or a valid result?
- Both. It signals no match, which may be expected or a data problem you should fix.
- How is IFNA different from IFERROR?
- IFNA catches only #N/A. IFERROR catches any error, which can hide #REF! or #VALUE! you need to see.
- Why does XLOOKUP return #N/A?
- The search key is not in the lookup array with the match mode you chose.
- Can MATCH return #N/A?
- Yes when the value is not found and you use MATCH without a third argument of 0 for exact match.
- Does #N/A break SUM?
- SUM ignores #N/A in some contexts but dependent charts may still fail. Clean lookups at the source.
- How do I show "Not found" instead of #N/A?
- Use IFNA(VLOOKUP(...), "Not found") or XLOOKUP with a not_found argument.
- Why #N/A after IMPORTRANGE?
- Usually a lookup on imported data with mismatched keys, not IMPORTRANGE itself.
- Can conditional formatting highlight #N/A?
- Yes. Custom formula ISNA(A1) or A1="#N/A" depending on how the error displays in your locale.
Related Tutorials
Watch how #N/A error works
Most Common Errors and How To Handle Them in Google Sheets
Related glossary terms
VLOOKUP
VLOOKUP searches the first column of a range for a key and returns a value from a specified column to the right. It is still everywhere in legacy templates even as XLOOKUP offers more flexible replacements.
Read guide →XLOOKUP
XLOOKUP searches a lookup array for a key and returns a matching value from a return array. It handles left lookups, approximate match, and custom not-found text without INDEX/MATCH gymnastics.
Read guide →MATCH
MATCH searches a range for a value and returns its relative position. Feeding INDEX or OFFSET with MATCH replaces brittle column index math. Sorted approximate match modes support tier tables and date brackets.
Read guide →FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →#VALUE! error
#VALUE! means Google Sheets cannot interpret an argument as the type the function expects. Common causes include text in a math formula, invalid dates, wrong delimiter locale, or passing a range where a scalar is required.
Read guide →Done reading about #N/A error?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.