Last Updated on January 9, 2024 by Jake Sheridan
Google Sheets VLOOKUP Syntax
The basic syntax of the VLOOKUP function is as follows:
=VLOOKUP(search_key, range, index, [is_sorted])
- search_key refers to the value VLOOKUP will search for in the first column of your specified range.
- range refers to the range of cells within which the search will be performed.
- index refers to the column index of the value to be returned. The count starts at 1 starting from the first column in range.
- [is_sorted] is an optional argument that indicates whether the column is sorted. Set this argument to FALSE to find an exact match. Set this argument to TRUE to find an approximate match if an exact match is not found. If this argument is omitted, VLOOKUP will set this value to TRUE by default.
5 Ways to use VLOOKUP in Google Sheets
Here are some different ways to use VLOOKUPs:
1. How to Vlookup from a different sheet
Here’s how you can perform a VLOOKUP function to retrieve data from a different sheet in your document.
In our sample spreadsheet, we have two sheets: HR_Data and Lookup.
The HR_Data sheet includes the data we want to use the VLOOKUP function on.
The Lookup sheet will contain a VLOOKUP function to output an employee’s contact information based on the employee ID input in cell B1.
Type the VLOOKUP function to start the formula.
Next, enter the value you want to search for.
After your lookup value, add a comma, then specify the range in the source sheet where the lookup will occur.
Make sure to include the source sheet’s name followed by an exclamation point and the actual cell range.
To search the range A1:F101 in the sheet HR_Data, we’ll use the cell reference HR_Data!A1:F101.
Insert the number of the column in the specified range that contains the data you want to retrieve.
Remember, the first column in your range is 1.
Add FALSE for an exact match or TRUE for an approximate match.
For most text-based lookups, FALSE is used.
Press Enter to execute the VLOOKUP formula.
If a match is found in the other sheet, the cell will display the equivalent value found in the specified column to retrieve.
We can copy this formula and change the column index argument to return other fields.
2. How to Vlookup with wildcard characters
Wildcards are special characters that represent one or more characters in a text string.
We can use wildcard characters in VLOOKUP formulas to retrieve information based on partial matches.
Here’s how to use VLOOKUP with wildcard characters:
Ensure that your data is organized in a table format where the first column is the data you want to search through.
Identify which column you want to retrieve from this lookup table.
In this example, we want to search through the email_address column for a match.
We also want our VLOOKUP function to return the phone number of the matching email address.
Select an empty cell and type the VLOOKUP function.
Next, we’ll provide a search key with a wildcard character.
The asterisk (*) character represents any number of characters while the question mark (?) represents a single character.
In this example, we want to find an email containing the string “archard”.
However, since we do not know how many characters come before or after this string in the target email, we’ll place the asterisk wildcard before and after the string.
Next, we’ll select the range to search through.
For this example, we’ll search through the range A2:F101.
Add a comma, then specify the column index number in the range from which to retrieve the data.
Since we want to return the value in the phone number field, we’ll input 5 as our column index number to return.
Add another comma and type FALSE to ensure an exact match is found.
Press Enter to execute the formula.
The cell will display the result from the VLOOKUP search.
Our formula =VLOOKUP(“*archard*”, A2:F101,5,FALSE) was able to find an email with the text “archard” and return the corresponding phone number.
3. How To Index Match for Left Vlookup
If the data you want to retrieve is on a column to the left of where your lookup value is, you will not be able to use VLOOKUP.
An alternative is to use INDEX and MATCH together to perform a leftward lookup.
Click on the cell where you want your lookup result to appear.
Type =INDEX(, which is the function you’ll use to return the value you want.
Input the range of cells that contains the data you want to retrieve.
For example, if the data you want to retrieve is in the range A2:A100, we can use the formula =INDEX(A2:A100.
After specifying the return range in INDEX, type a comma, then the MATCH function.
Follow this with the desired lookup value or the cell that contains it.
In this example, we’ll use the value in cell I2 as our lookup value.
After the lookup value in MATCH, add a comma and then enter the range of cells that contains the lookup values.
Type another comma and then ‘0’ to indicate you want an exact match.
Press Enter to run the formula.
The cell should now show the data from the INDEX range that corresponds to your MATCH criteria.
4. Case-sensitive Google Sheets Vlookup formula
Since the VLOOKUP formula in Google Sheets is case-insensitive, you may want to find a workaround formula that performa a case-sensitive lookup.
First, select the cell where you want to add the lookup formula.
In this example, we want to add a lookup formula in cell F2.
This formula will try to use the search key in cell F1 to find a corresponding phone number using the dataset in columns A to C.
We can use the INDEX, MATCH, and EXACT functions together to create a case-sensitive lookup formula.
In the example above, we used the formula =INDEX(C1:C101, MATCH(TRUE, EXACT(F1, A1:A101), 0)).
The EXACT function allows us to compare two strings to see if they are exactly the same (which also includes case).
EXACT(F1, A1:A101) compares the value in cell F1 (‘Archard’) with each cell in the range A1:A101.
The result of this function is an array of TRUE and FALSE values.
This array is then used as input in the MATCH function.
Instead of using MATCH to match the actual text values, we’ll just use MATCH to find which row has a value of TRUE.
Once MATCH finds the position within the range A1:A101, the INDEX function will use that position to output the corresponding value in the column C.
Hit the Enter key to evaluate the formula.
In our example above, we were able to find an entry in our dataset that has a last_name value that matches “Archard”.
If we search for “ARCHARD”, our custom formula returns an error, indicating that no match with the exact case was found.
5. How do I do a VLOOKUP text in Google Sheets?
Here’s how you can perform a VLOOKUP on text in Google Sheets.
First, ensure that your source data (the data you want to look up) is properly organized on one sheet.
The lookup value should be in the first column of your data range.
In the example above, we have a table from the HR department showing employee contact information.
We’ll use the employee ID as our lookup value to return the corresponding phone number.
In your destination sheet, click on the cell where you want the VLOOKUP result to display.
Type =VLOOKUP( to start your formula.
In this example, we’ll output our phone number in cell I2.
Next, enter the lookup value as the first argument.
This is the text you want to search for in your source data.
For example, we’ll type =VLOOKUP(I2 since we’ll use the value in cell I2 as our lookup value.
Specify the range of the source data as the second argument of VLOOKUP.
Add the column number from your range where the corresponding data is located.
Remember, the first column of your range is 1.
In our example, the phone number field is the fifth column in our lookup table range.
Next, specify FALSE for the final argument.
This will ensure that VLOOKUP will find the exact match of the lookup text provided.
Hit Enter to evaluate the VLOOKUP function.
How do I do a VLOOKUP between two Google Sheets?
Performing a VLOOKUP between two Google Sheets simply requires adjusting the cell references.
To reference a lookup table on another sheet, place the sheet name followed by an exclamation point first before the cell range.
For example, if you’re looking up data in the cell range A1:E100 in a sheet labeled Sheet1, you can use the cell reference Sheet1!A1:E100.
Hopefully this guide has given you an overview of using VLOOKUPs in Google Sheets.
What’s Next?Explore some of the other useful resources on Sheets for Marketers:
- Google Sheets Templates – A collection of over 200 Google Sheets templates for SEO, marketing, PPC and more.
- Google Sheets Tutorials – A growing collection of spreadsheet tutorials for doing (almost) everything in Google Sheets.
- Data Studio Templates – A collection of over 50 Google Data Studio templates for SEO reporting.