What Are VLOOKUP Errors in Google Sheets?
VLOOKUP errors in Google Sheets occur when the formula is not structured properly or there is an issue with the data provided. Some of the common errors we encounter when using VLOOKUP include #N/A, #NAME?, and #VALUE! Errors. Additionally, other errors can occur, particularly with data types, range references, and column indices.
The VLOOKUP function is very versatile. It is quick and simple to find information in Google Sheets when the data is organized in columns. But, since the function requires a few arguments, anyone can make a mistake if they are not careful when writing a VLOOKUP formula. In the example below, we get the #REF error as the index is given as 3, whereas the range has only two columns. This article explains in detail the most frequent errors with VLOOKUP Google Sheets and how to troubleshoot them.
Key Takeaways
- We must fix VLOOKUP errors because they disrupt the accuracy of the data and cause reports to be incorrect.
- VLOOKUP can produce different errors such as
- a. #N/A when value isn’t found,
- b. #VALUE! when the column index is invalid
- c. #REF! when the column index exceeds the range
- To avoid errors, the lookup value should be in the leftmost column of the range, the column index is a valid integer within the range bounds, and we use the right match parameter.
- You can handle these errors using functions like IFERROR, IFNA, and TRIM for cleaner formulas.
The Most Common VLOOKUP Errors in Google Sheets
Though VLOOKUP is straightforward and commonly used, errors occur commonly due to a misunderstanding when entering the parameters. Let us look at some of the most common errors using VLOOKUP.
1. #N/A Error
When you do a search using the VLOOKUP, if the formula can’t find anything, it returns the #N/A error.
Solution: If you are expecting the value to be present, you must have entered the data in the formula or your table incorrectly. Check both places to see if the data/formula value has been entered correctly.
2. #VALUE! Error
This error occurs when a cell reference contains an incorrect data type or invalid data.
Solution: Verify the cell reference value and ensure the data is in the correct format for VLOOKUP.
3. #REF! Error
The #REF! error occurs when the column index number given as an argument is greater than the number of columns in the lookup range.
Solution: Make sure that the column index in VLOOKUP is within the range provided by counting the columns in the table and adjusting the index accordingly.
4. #NAME? Error
This type of error occurs when Google Sheets cannot recognize a function or an argument. It usually occurs when the name is incorrect or there is a typo error. n
Solution: Double-check the syntax of the VLOOKUP formula, including capitalization and the parentheses.
5. #ERROR! Error
This error may occur when the formula has a syntax error, invalid reference, or incorrect data type.
Solution: Check the formula for errors or any syntax issues, such as incorrect commas, parenthesis, quotation marks, and operators.
How To Fix VLOOKUP Errors in Google Sheets?
Let us look at how to fix VLOOKUP errors in Google Sheets. Always check the formula syntax, data, index, and range selection. Besides, you should also check if the lookup value is accurate and if the range includes all relevant columns. Also, verify the column index number and whether an exact or approximate match is required.
Here’s a stepwise guide on how to do it.
Step 1: Our first step is to identify the error. One must look for the error message displayed in the cell like #N/A or #VALUE!. This would give us a hint of why the error occurred.
Here, we see that it is an #REF error. This could mean that there is a mistake in the index number of the column or the range specified.
Step 2: Next, examine the formula. Look up the syntax in detail.
=VLOOKUP(lookup_value, lookup_range, column_index, [is_sorted]).
Ensure each argument is correctly specified.
=VLOOKUP(A10,B1:C8,3,FALSE)
On the surface, it appears to be fine.
Step 3: Check the lookup value. Ensure that the value you are searching for in the lookup range is accurate. For example, if searching for 234, ensure that 234 is entered in the formula.
Here, the lookup value seems fine.
Step 4: Next, check the lookup range. The range we specify should include the column we are searching for and the column from which we will expect the return value. If the search key is not in the first column of the range, it may lead to inaccurate results.
Here, is where we notice the error. The lookup value is in Column A, while we have mistakenly specified Column B and C.
Let us change the range to A1:C8.
Step 5: Check the column index to see if the index number is correct. It represents the column number in the lookup range from which we must retrieve the data. The leftmost column is 1, then 2, then 3, etc.
Here, the index number of 3 is fine as we are looking for the score from Column 3.
Step 6: Determine if you need an exact match (FALSE) or an approximate match (TRUE) for your search. This is specified as the last argument.
Now, press Enter and check the result.
You get Priya Sharma’s score of 115 and our issue has been resolved.
Similarly, you can try fixing VLOOKUP errors in Google Sheets by working your way backward from the error message.
Examples
Let us look at the different methods of fixing VLOOKUP errors in Google Sheets with a few examples.
Example #1 – Fix #N/A error in VLOOKUP in Google sheets
Let us enter the data in a table in columns A and B. Here, column A lists product codes, and column B lists prices. Let us type the required product code in a cell and retrieve its price. If the code isn’t found, you get a #N/A error, which should also be fixed.
Step 1: First check if the lookup value you are entering is in the leftmost column of the table. Next, enter the right range which includes the column from where we will look up the entered value till the column from which you wish to retrieve the data.
Finally enter the right column index from which you wish to retrieve the corresponding value. The last argument specifies whether you want an exact or approximate match.
Enter the search value in cell D2 and enter the following formula in cell D3.
=VLOOKUP(D2, A2:B8, 2, FALSE)
Step 2: Wrap the VLOOKUP in IFNA to handle missing matches. The function can be modified as follows:
=IFNA(VLOOKUP(D2, A2:B10, 2, FALSE), “Not found”)
This formula returns “Not found” instead of #N/A when no match is found.
This prevents errors and improves readability.
Example #2 – Fix #VALUE! error in VLOOKUP in Google Sheets
As an extension of the previous example, we have a table with columns from A to C. We have the product IDs in Column A, their names in B, and the corresponding stock in C.
Step 1: Let us enter a lookup value which is an ID in cell E2.
Step 2: We must write the column index as 3. Let us enter the value as three. Enter the following formula in cell E3.
=VLOOKUP(E2, A2:C10, three, FALSE)
Step 3: When you press Enter, you get the #NAME? error. To fix this, wrap the formula in IFERROR() to handle mistakes:
=IFERROR( VLOOKUP(E2, A2:C10, three, FALSE), “Invalid column index or error”)
This ensures any error is replaced with the custom message keeping the sheet cleaner.
Example #3 – Fix #REF! error in VLOOKUP in Google Sheets
Consider the same table as above. We are to find the stock when we enter the product ID.
Step 1: Enter the following formula in an empty cell.
=VLOOKUP(E2, A2:B10, 3, FALSE)
Google Sheets returns a #REF! error. It happens when the column index exceeds the number of columns in the specified range.
Step 2: To fix this, expand the range or enter the right index number. For instance, change the range to A2:C10.
=VLOOKUP(E2, A2:C10, 3, FALSE)
Step 3: One can wrap the formula in IFERROR to catch unexpected reference errors and display a custom message:
=IFERROR(VLOOKUP(E2, A2:C10, 3, FALSE),”Check the parameters”)
If it is still not fixed, check if any column has been mistakenly shifted or deleted. After fixing the range, now press Enter.
Important Things to Note
- If you mistakenly specify a range that is bigger than the maximum number of columns in the range, you get a #REF error.
- To handle scenarios when you may get an error due to not finding the lookup value, you can use the IFERROR function.
- If VLOOKUP Google Sheets return wrong values, set the fourth argument to FALSE to return exact matches.
Frequently Asked Questions (FAQs)
If using approximate matches, that is, the fourth argument is set as TRUE, we must ensure that the first column is sorted in ascending order. Otherwise, the result will be incorrect, and we will encounter VLOOKUP not working in Google Sheets.
One can handle the #N/A error using the IFNA function. Using this, one can display a custom message if we find no match. For instance, =IF(ISNA(VLOOKUP(A2, A2:C10, 3, TRUE)), “Not Found”, VLOOKUP(A2, A2:C10, 3, TRUE)). This function checks for the #N/A error. If true, it prints “Not Found.” If FALSE, it prints the value retrieved.
Sometimes, there will be no reason for fixing VLOOKUP errors in Google Sheets as we may not get an error message, but an incorrect value. This could be due to:
Incorrect search mode: Set the search option to TRUE or FALSE accurately depending on your requirement.
Duplicates in Column 1: If the first column contains duplicates, the value retrieved might be different from your expectations.
Lookup column not sorted: If the fourth argument is TRUE, we must sort the first column for accurate results.
Download Template
This article must help understand Fixing VLOOKUP Errors in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to Fixing VLOOKUP Errors in Google Sheets. We learn the ways on how to fix different VLOOKUP errors like #DiV/0 and #N/A with examples. You can learn more from the following articles. –
Compare and Match Columns in Google Sheets
Leave a Reply