VLOOKUP errors in Google Sheets usually come from a few common causes: the lookup value isn’t in the first column of the range, the range isn’t sorted (or you used approximate match), or the data types don’t match. This guide walks through each error type—#N/A, #REF!, #VALUE!, and #NAME?—and shows you exactly how to fix them, including using IFERROR and switching to XLOOKUP when VLOOKUP isn’t the right tool.
Why VLOOKUP Returns Errors
VLOOKUP searches for a key in the first column of a range and returns a value from a column you specify. It fails when the key isn’t found, when the range is set up wrong, or when you ask for a column number that doesn’t exist. The most common error is #N/A, which simply means “not found.” That can happen because the value truly isn’t there, or because of a subtle mismatch like a number stored as text, extra spaces, or a different date format.
Another frequent issue is using approximate match (the last argument is TRUE or omitted) when your data isn’t sorted. VLOOKUP assumes the first column is sorted ascending when you use approximate match; if it isn’t, you get unpredictable results or #N/A. The fix is either to sort the first column or to use FALSE for exact match.
Understanding the VLOOKUP Syntax
The syntax is: =VLOOKUP(search_key, range, index, [is_sorted])
- search_key – the value to look for (e.g., a product ID or name).
- range – the table to search, with the lookup column as the first column.
- index – the column number in the range to return (1 for the first column, 2 for the second, etc.).
- is_sorted – FALSE for exact match (most common), TRUE or omitted for approximate match.
If you omit the last argument, Google Sheets defaults to TRUE (approximate), which is a common source of #N/A errors when your data isn’t sorted. Always use FALSE unless you specifically need the closest match.
Common VLOOKUP Errors and How to Fix Them
#N/A – Value Not Found
This is the most frequent error. Check these things in order:
- Is the value actually in the first column? VLOOKUP can only search the leftmost column of the range. If your lookup column is not the first, reorder your data or use INDEX/MATCH or XLOOKUP.
- Are there extra spaces? Use
=TRIM(A2)on both the lookup value and the data column, or use wildcards:=VLOOKUP("*"&A2&"*", range, 2, FALSE). - Is the data type the same? Numbers stored as text won’t match numbers. Convert text to numbers with
=VALUE()or use=VLOOKUP(A2&"", range, 2, FALSE)to force text. - Is the lookup value truly absent? If it’s not there, VLOOKUP can’t invent it. Consider using IFERROR to display a friendly message.
#REF! – Column Index Out of Range
This error means your index number is larger than the number of columns in the range. For example, if your range is A:B and you ask for column 3, you get #REF!. Fix: count the columns in your range and make sure the index is within that count. Also check that you haven’t deleted a column that was part of the range.
#VALUE! – Wrong Data Type or Range
This often happens when the index is 0 or negative, or when the range is not a valid rectangle. It can also occur if you’re comparing incompatible types (e.g., text vs. number) in a way that Sheets can’t handle. Double-check that the range is a contiguous block and the index is a positive integer.
#NAME? – Typo in Function Name
This is simply a misspelling, like =VLOOOKUP. Check the spelling and make sure you haven’t accidentally used a semicolon instead of a comma (depending on your locale).
Using IFERROR to Hide Errors
Instead of seeing ugly #N/A, you can wrap your VLOOKUP in IFERROR to show a custom message or a blank cell:
=IFERROR(VLOOKUP(A2, B:C, 2, FALSE), "Not found")
This is useful for dashboards or reports where you don’t want error codes. It also lets you chain fallbacks: =IFERROR(VLOOKUP(...), VLOOKUP(...)) to try multiple lookup tables.
When to Use XLOOKUP Instead
XLOOKUP (available in Google Sheets since 2022) is more flexible and eliminates many VLOOKUP limitations. Its syntax is:
=XLOOKUP(search_key, lookup_range, result_range, [if_not_found], [match_mode], [search_mode])
Key advantages:
- You can search any column, not just the first.
- You can return values from columns to the left of the lookup column.
- It defaults to exact match, so no accidental approximate matches.
- You can specify a custom “not found” message directly.
If you’re starting a new spreadsheet, use XLOOKUP. For existing files, you can replace VLOOKUP with XLOOKUP quickly—the logic is often simpler.
Alternatives: INDEX/MATCH and QUERY
INDEX/MATCH is another powerful combination that works in both Excel and Sheets. It’s more flexible than VLOOKUP because you can look up to the left:
=INDEX(C:C, MATCH(A2, B:B, 0))
This finds the position of A2 in column B and returns the corresponding value from column C. The 0 in MATCH forces exact match.
For complex lookups or when you need to filter data, QUERY is a SQL-like function that can do joins and lookups in one step. For example:
=QUERY(A:C, "SELECT C WHERE B = '"&A2&"' LIMIT 1", 0)
This is overkill for simple lookups but useful when you need to combine multiple conditions.
Frequently Asked Questions
Why does VLOOKUP return the wrong value instead of an error?
This happens when you use approximate match (TRUE or omitted) and your first column isn’t sorted. VLOOKUP then returns the closest match that is less than or equal to the lookup value, which may not be what you want. Always use FALSE for exact match unless you intentionally need the closest value (e.g., for tax brackets).
Can VLOOKUP look to the left?
No, VLOOKUP can only return values from columns to the right of the lookup column. If you need to return a value from a column to the left, use INDEX/MATCH or XLOOKUP.
How do I fix #N/A when the value exists?
Check for hidden characters, spaces, or data type mismatches. Use TRIM to remove spaces, and ensure both the lookup value and the data column are the same type (both text or both numbers). You can also use wildcards to do a partial match.
What does the is_sorted argument really do?
It tells Google Sheets whether to use an exact match (FALSE) or an approximate match (TRUE). With TRUE, the first column must be sorted ascending; otherwise, results are unpredictable. With FALSE, no sorting is needed, but it’s slower on large datasets.
Is there a limit to how many rows VLOOKUP can handle?
Google Sheets supports up to 10 million cells, so VLOOKUP can handle large ranges, but performance may slow down. For very large datasets, consider using QUERY or a pivot table for faster lookups.
Can I use VLOOKUP across different sheets?
Yes, you can reference another sheet in the range, like =VLOOKUP(A2, 'Sheet2'!A:B, 2, FALSE). Make sure the sheet name is in single quotes if it contains spaces.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- Hogwarts Legacy Microsoft Visual C Runtime Error Fix
- Set Up UPI Payments Through WhatsApp
- Pair Oppo Enco Earbuds with Android, iPhone & Windows
- Fix Administrator Account Disabled Error in Windows 11
- How to Play Kodi on Smart TV from PC
- Factory Reset on Samsung Galaxy: What It Erases