In this article:

Did Not Find Value in VLOOKUP Evaluation in Google Sheets

did not find value in vlookup evaluation google sheets

Did Not Find Value in VLOOKUP Evaluation in Google Sheets

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])

  • search_key is the value to search for.
  • range is the range of cells that contains the data.
  • index is the column index of the value to be returned, with the first column in range being numbered 1.
  • [is_sorted] is an optional argument that indicates whether the column to be searched is sorted. If TRUE or omitted, VLOOKUP will perform an approximate match. If FALSE, it will perform an exact match.

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.

Troubleshooting Google Sheets ‘Did Not Find Value’ in VLOOKUP Evaluation

Here are a number of troubleshooting tips to address the “did not find value ‘ ‘“ in VLOOKUP Evaluation in Google Sheets. 

1. Double-Check the Search Key

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).

2. Verify the Range

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.

3. Consider Data Formats

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.

4. Use Helper Functions for Data Cleanup

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.

5. Error Checking

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.

Example Correction Formula

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. 

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started