The Ultimate Guide to VLOOKUP in Google Sheets

If you’re working with volumes of interrelated data in spreadsheets, finding and referencing information across tables is often challenging. That’s where VLOOKUP comes in. VLOOKUP is a powerful, but sometimes overlooked, function in Google Sheets that retrieves matching data between two distinct tables. 

When used properly, VLOOKUP can decomplexify spreadsheet processes and speed up data analysis. In this guide, we’ll cover the nuts and bolts of VLOOKUP, including how to use it in Google Sheets.

What is VLOOKUP in Google Sheets?

VLOOKUP is a search function that finds data in one table and uses this data within another table. The tables can occupy the same sheet, a different tab, a separate sheet, or a spreadsheet file. The function searches a specified column vertically for a search key (hence VLOOKUP), then returns the value from the same row. 

Google Sheets VLOOKUP: Syntax & Function

Let’s break down the basics of a VLOOKUP formula to better understand how it works. Here’s an overview of the standard syntax:  

VLOOKUP(search_key, range, index, [is_sorted])

  • index is the column containing the value you’re looking for. For instance, if you want to find the product price in a table, this would be the price column. The index value should be between 1 (first column) and the total number of columns.
  • search_key is the item or value you want to find. For instance, if you’re looking for a product in your inventory table, the search_key could be the item number or product name in the same row. 
  • range refers to the range you want to include in the VLOOKUP function or the data columns.
  • is_sorted refers to the final value and is true or false (true by default). It is used to indicate whether the index column has been sorted.  

VLOOKUP in Google Sheets: Key Points

While VLOOKUP is a powerful function, it’s not without limitations. Consider these factors before using VLOOKUP to avoid potential errors.  

  • VLOOKUP is not case sensitive. The function does not distinguish between uppercase and lowercase letters. 
  • VLOOKUP does not search columns to the left of the specified range. To search columns to the left, you must use an Index Match formula. 
  • When is_sorted is set to TRUE, you must sort the range’s first column in ascending order. 
  • VLOOKUP can search using partial matches based on wildcard characters: the asterisk (*) and question mark (?).
  • Set the is_sorted to FALSE to return exact matches. For instance, if your VLOOKUP from another sheet returns incorrect results.  

How to use VLOOKUP in Google Sheets 

Now that you understand the function better, let’s actually get into the nuts-and-bolts of using VLOOKUP. Here are several ways you can use VLOOKUP in Google Sheets. 

1. VLOOKUP within the Same Google Sheet

Let’s say you have a Sheet containing your product IDs, a pricing schema, prices, and subscriptions for your SaaS products. 

SAAS Products

You want to produce another table within the same Sheet that only includes the product ID and price. To do this, you can use VLOOKUP to extract the price from the spreadsheet using only the product ID number. Here’s how. 

Step 1: Create another column for the Product ID and Price. 

Input the product ID number you want to find, then in the field right next to it under the Price column, type in =VLOOKUP, and enter the formula’s parameters.  

vlookup formula parameters

Step 2: Input the search key 

Enter the product ID value into the formula or click the cell (F2) to automatically add it. Then add a comma.

vlookup search key

Step 3: Add the range (second parameter) and specify the set of columns you want to include in the search. 

Click the first column header and drag your mouse to the table’s last column, or type in A:D, then type a comma.   

vlookup columns

Step 4: Input the column containing the value you’re looking for

Enter the number of the column that contains the value you’re looking for. The price is the third column in the sequence, so type ‘3’. Then add a comma after. 

vlookup sequence

Step 5: Enter ‘False’ for is_sorted

Add False after the last comma and include a closing parenthesis. 

Your final formula should look like this:

=VLOOKUP(F2,A:D,3,FALSE)

vlookup is_sorted

Step 6: Execute VLOOKUP formula

Press Enter, and you should see the result appear in the cell with the VLOOKUP formula. 

vlookup formula

Step 7: Drag down VLOOKUP formula

Drag the cell down to copy the formula to the rest of the cells in the column. 

vlookup drag down

You’ll get the data you need quickly without manually looking up each corresponding value and inputting them to the new table. 

2. VLOOKUP with Wildcard Characters

If you only know part of a search_key, perform VLOOKUP for partial matches using these wildcard characters:

  • A question mark (?) to match single characters
  • An asterisk (*) to match character sequences

Let’s say you want to retrieve information about a specific product from the same table.

vlookup partial matches

If you don’t know the entire product ID, but remember the first character “A,” use an asterisk to fill in the missing part in your formula:

=VLOOKUP(“A*”,A:D,1,FALSE) 

vlookup asterisk

Press Enter and see the output: 

vlookup false

Create a more flexible VLOOKUP formula by entering the known part of the search key in a cell. Then string that cell with ‘*’. Next, set the is_sorted perimeter to TRUE.   

For instance, you can pull the corresponding pricing plan using this formula:

=VLOOKUP($F$1&”*”,A:D,2,True) 

vlookup true

To pull the actual price, use this formula: 

=VLOOKUP($F$1&”*”,A:D,3,True) 

3. Case-Sensitive VLOOKUP

As mentioned, VLOOKUP does not distinguish between lower and uppercase letters.

If you need to do a case-sensitive lookup, use INDEX MATCH with the EXACT and TRUE functions in your Google Sheets VLOOKUP array formula: 

ArrayFormula(INDEX(return_range, MATCH (TRUE,EXACT(lookup_range, search_key),0)))

Using the sample table below, let’s assume that the search key is in cell A3, the lookup range is G3:G8, and the return range in F3:F8. 

vlookup range

The formula should look like this:

=ArrayFormula(INDEX($F$3:$F$8, MATCH (TRUE,EXACT($G$3:$G$8, A3),0))) 

The formula can distinguish between lowercase and uppercase characters (a-1001 and A-1001). 

vlookup table

4. VLOOKUP on another Sheet within the Same Spreadsheet File

Suppose you have an ‘Employees’ table in one Sheet, and a ‘Sales’ table in another Sheet. 

vlookup in different sheet

Here’s how to retrieve the Hourly Rates that correspond to Employee IDs E010 and E040 from the Employees Sheet, and display them in cells B3 and B4 of your Sales sheet. 

Step 1: Select target cell

Click on your target column’s first cell (cell B3 in the Sales Sheet).  

vlookup sales table

Step 2: Define the search key

Input this:

=VLOOKUP(A3, 

A3 is the cell that contains the value you want to look up. 

vlookup search key

Step 3: Set the search range to specific columns on the other Sheet

Input the search range by clicking and opening the Employees tab. 

Select the cell range you want the VLOOKUP function to search in (cell ranges A3:C8). 

Lock the cell range by pressing F4 (or Ctrl+F4) on your keyboard. This ensures these references won’t change when the formula is copied to other rows within the column. 

The VLOOKUP formula should now show as:

=VLOOKUP(A3,Employees!$A$3:$C$8

vlookup lock cell range

Step 4: Input the VLOOKUP index

Add a comma after 8, followed by the column’s index containing the values you want to retrieve. 

Input the number ‘3’ since we’re retrieving the Hourly Rates (the third column in the A3:C8 range). Add the closing parenthesis. 

vlookup column index

Press Enter, and you should see the Hourly Rate that corresponds to Employee ID E010 in cell B3 in the Sales Sheet. 

Step 5: Drag down the formula to fill the rest of the cells in the column. 

vlookup drag down formula

5. VLOOKUP on Another Sheet in a Separate Spreadsheet File

Doing a VLOOKUP in another Sheet on a separate spreadsheet file uses the same function as the example above.

However, there’s a slightly different second parameter — it includes the IMPORTRANGE function. 

The IMPORTRANGE function is generally used to import values from cells in a separate spreadsheet into your current sheet. 

It includes two parameters:

  • spreadsheet_key is the spreadsheet URL you want to import from, and it should be specified in double-quotes (“ “). 
  • range_string is a reference to the cell ranges you want to import. It should contain the Sheet name, including the cells you want. For example, if you’re importing cells A3:C8 from the Employees worksheet in the previous example, your range_string should be “Employees!A3:C8”.

Let’s assume you have:

  • An ‘Employee’ table in a Sheet named ‘Employees’ within a spreadsheet file called ‘Ss1’
  • A ‘Sales’ table in a Sheet named ‘Sales’ within a separate spreadsheet file called ‘Ss2’  

In this example, we’ll retrieve the Hourly Rates that correspond to employee IDs E010 and E014 from the Employees Sheet in Ss1 and display them in cells B3 and B4 on the Sales Sheet in Ss2. 

Here’s how to do it. 

Step 1: Click B3 on the Sales table within Ss2. 

vlookup separate spreadsheet

Input =VLOOKUP(, and select cell A3 followed by a comma.

vlookupdifferentspreadsheet

Step 2: Deploy IMPORTRANGE in the formula

Enter IMPORTRANGE function as the second parameter, then an opening parenthesis. 

vlookup importrange

Step 3: Open Ss1 and select the Employees Sheet

vlookup tips

Copy the Google Worksheet’s URL from your browser’s address bar.   

how to do vlookup

Step 4: Insert URL into IMPORTRANGE

Go back to the Ss2 spreadsheet and paste the URL into the IMPORTRANGE function. Be sure to enclose the URL in double quotes.  

importrange vlookup

Add a comma after the double quotation mark followed by the source Sheet’s name — ‘Employees’ in this case.  

Step 5: Input Lookup Ranges

Add an exclamation mark (!) and input the cell ranges ($A$3:$C$8) you want to look up from the source Sheet. 

Enclose the entire parameter (source sheet name, exclamation mark, and cell range) in double quotes followed by a comma and closing parenthesis (to close the IMPORTRANGE function). 

The formula is now: 

=VLOOKUP(A3,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Od_qTN0ze4nvh4cfoDjYCHxPJmdjAqTEABrvLom0KM4/edit#gid=870777933″,”Employees!$A$3:$C$8”)

vlookup how to

Step 6: Add the column index

Add a comma and the column index containing the values you’re retrieving — the Hourly Rates in the third column of the A3:C8 range.

Input the number ‘3’ followed by a closing parenthesis, then press Enter. Now the formula is:

=VLOOKUP(A3,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Od_qTN0ze4nvh4cfoDjYCHxPJmdjAqTEABrvLom0KM4/edit#gid=870777933″,”Employees!$A$3:$C$8”),3)

Step 7: Solving #REF! Errors

If you see a #REF! Error, click the Allow access button to connect the Sheets. 

You should now see the hourly rates that correspond to Employee ID E010 in cell B3 of your Sales table. 

Drag down down the formula to fill in the rest of the cells under the column. 

how to do vlookup in google sheets

VLOOKUP can be a powerful function once you figure out how to use it to reference information across your Google spreadsheets. Data changes in the original Sheet get automatically updated in your connected cells, making it easy to reference data on multiple Google spreadsheets quickly. 

6. Index match formula for leftward VLOOKUP

The VLOOKUP function cannot search to the left. This means if the search column isn’t the first column in the lookup table, your Google Sheets VLOOKUP won’t work. 

However, you can work around this using a more durable and powerful Index Match formula:

INDEX (return_range, MATCH(search_key, lookup_range, 0))

For instance, you can use this formula: 

=INDEX($F$3:$F$8, MATCH (A3, $G$3:$G$8, 0))

The formula lets you look up the A3 cell value (search_key) in G3:G8 (lookup_range) and return a match from F3:F8 (return_range). 

vlookup example

Unlike VLOOKUP, the Index Match formula is unaffected by structural changes within Sheets since it directly references the return column.  

For instance, deleting or inserting a column in your lookup table breaks your VLOOKUP formula since the hard-coded index number becomes invalid. This isn’t the case with Index Match formulas.  

VLOOKUP Eliminates Squint-and-Point Searches for Data

VLOOKUP eliminates the hassle of manually finding and referencing cells in Google Sheets. That’s why Google Sheets users of all backgrounds, from scientists to payroll administrators, consider VLOOKUP indispensable. Although VLOOKUP comes in many iterations, the result is always the same: you save time and energy by avoiding gruntwork. Now you can focus instead on the tasks that really matter. 

Wait, there's more!

Connect any system to Google Sheets in just seconds.

Get Started Free
40,000+ users on
Google Marketplace
Trusted by thousands
of companies