In Google Sheets, when using the VLOOKUP function to search for a specific value in a column and return a value from a corresponding column, you might encounter the "Did Not Find Value" error. This error occurs if the VLOOKUP function cannot find a match for the lookup value in the first column of the specified range.
The VLOOKUP function in Google Sheets follows the syntax:
VLOOKUP(search_key, range, index, [is_sorted])
The "Did Not Find Value" error specifically arises when [is_sorted] is set to FALSE (indicating that an exact match is required), and the function fails to find an exact match for the search_key in the first column of the specified range.
Here are a number of troubleshooting tips to address the “did not find value ‘ ‘“ in VLOOKUP Evaluation in Google Sheets.
Exact Match: Ensure your search key exactly matches one of the entries in the first column of your range. Even minor differences, such as extra spaces or case differences, can cause the VLOOKUP to fail.
Trim Spaces: Use the TRIM() function to remove any leading, trailing, or double spaces within your cells. For example, if your search key is in cell A1, you could use =TRIM(A1) as the search key.
Case Sensitivity: Google Sheets is case-sensitive. If there's a possibility of case mismatch, consider using LOWER() or UPPER() functions on both your search key and the range data to standardize the case. For instance, use =VLOOKUP(LOWER(A1), LOWER(range), index, FALSE).
Correct Range: Confirm that the range you've specified in the VLOOKUP includes the correct columns and rows. The first column of this range is where VLOOKUP searches for the search key.
Absolute References: Ensure your range is correctly referenced. It's often useful to use absolute references (e.g., $A$1:$B$10) to prevent changes in the range reference when copying or dragging formulas.
Matching Formats: The format of the search key and the data in the lookup range must match. If one is formatted as text and the other as a number (or vice versa), VLOOKUP might not find the match. You can use the TEXT() function to format numbers as text or use value conversion functions like VALUE() to convert text to numbers if necessary.
TRIM() for Spaces: As mentioned, TRIM() can remove unwanted spaces that might cause mismatch issues.
UPPER() or LOWER() for Case Sensitivity: Apply these functions to both the search key and the range data to avoid case-sensitive issues.
PROPER() for Name Formats: If your data includes names or titles, PROPER() can standardize the capitalization.
Check for Errors in Data: Ensure there are no #N/A, #VALUE!, or other errors in your range that could affect the VLOOKUP search.
Manual Verification: Try to manually locate the search key in the first column of the range to confirm it actually exists and is not overlooked due to a subtle difference.
If you're dealing with potential case and space discrepancies, your VLOOKUP might be modified as follows:
=VLOOKUP(TRIM(LOWER(A1)), ARRAYFORMULA(TRIM(LOWER(B2:C100))), 2, FALSE)
This formula ensures that both the search key and the range data are in lower case and free of leading/trailing/double spaces which significantly increases the chances of a successful match.
We hope that you now have a better understanding of how to troubleshoot the ‘Did Not Find Value’ in VLOOKUP evaluation in Google Sheets. If you enjoyed this article, you might also like our article on how to insert a date picker in Google Sheets or our article on VLOOKUP in Google Sheets.
If you want to learn how to swap columns in Google Sheets, we also suggest checking out our detailed guide.
