How to use VLOOKUP in Google Sheets

Having to use formulas and functions to navigate data in Google Sheets used to send a cold shiver down my spine. I’d call in for backup (my developer partner) and watch in awe as he used formulas to display data in two minutes that would have taken me two hours to create manually.

But alas, I’ve lived, laughed, and learned, and now I know how to do some basic functions, including VLOOKUP, which lets you get data from one part of a spreadsheet to another without adding it manually.

Here, I’ll go over how to use VLOOKUP in Google Sheets step by step, plus give you some tips to make the function even more powerful. Make a copy of our demo spreadsheet to follow along as I walk you through the tutorial. 

Table of contents:

What is the VLOOKUP function in Google Sheets?

Imagine you have a big table in Google Sheets with thousands of employee names and ID numbers, and you need to pull that employee information into another part of the spreadsheet, like an organizational chart or performance reviews. VLOOKUP can quickly transfer information from one part of a spreadsheet into another by searching for information in one column based on a value you know from another column. 

Here are the components that go into the function: 

1. Table: You have a table with rows and columns of data. 

2. Value: You know a specific value, like a name or ID, and you want to find more related information. 

3. Lookup: You use VLOOKUP to look for that value in the same row but a different column. 

4. Retrieve: Once it finds the value, VLOOKUP gives you the information. 

Think of it like looking up a name in a phone book (remember those?). You find the name you’re searching for, and you can see the phone number right next to it. VLOOKUP does the same thing with data in Google Sheets.

VLOOKUP syntax and inputs

If your data is consolidated in one spreadsheet, rather than spread across multiple tabs, you’ll need to plug the following VLOOKUP formula into the cell where you want your result to populate:

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

Here’s what each of those inputs mean: 

• search_key: This is the value you’re looking for in your table. 

• range: This is the area you want to search for your value, or the section of your table where you think you’ll find the information. It has to be at least two columns. 

• index: This is a column number within the range you provided where your desired data is located. For example, if you’re looking up names and you want corresponding ages, the column number where ages are listed would be your index.

• is_sorted: This tells VLOOKUP if the data in the range is sorted (TRUE) or not (FALSE).

  • TRUE: If you set it to TRUE, Google Sheets will assume the data is in ascending order (A to Z or smallest to largest) and can search faster. But this also means it will search for a close but not exact match. So it’ll find the closest value that’s less than or equal to the lookup value.

  • FALSE: If you set it to FALSE, Google Sheets will search more thoroughly for an exact match. If VLOOKUP doesn’t find an exact match, it’ll return an #N/A error.

This formula is like saying to Google Sheets, “Here’s a value I want you to find in this specific part of the table. Once you find it, grab the information from this column. Also, the data [is/isn’t] sorted, so [look quickly/look carefully].” 

If you have data on multiple sheets, here’s what the function would look like:

=VLOOKUP(search_key,SheetName!range, index, [is_sorted])

Basically, directly before the range, you add the worksheet name that contains the data you want to pull from followed by an exclamation mark (!).

How to use VLOOKUP in Google Sheets

Now I’ll show you what VLOOKUP looks like in action using two basic columns of data.

If you needed to use VLOOKUP in real life, you’d probably be dealing with a much larger and more complex dataset. But for the sake of learning how to use the function, I’ll keep our example super simple with a small list of pretend employees and ID numbers. Our goal is to find the ID number of a specific employee. 

Follow along in the demo spreadsheet under the “Simple example – FALSE” tab. 

1. Organize your data. Enter your data into a spreadsheet or locate an existing table. (The data is already there for you in this spreadsheet.)

2. Select an output cell. Click the cell where you want the information you’re looking for to end up. In this case, click into cell 23A.

   

3. Enter the VLOOKUP function. Enter the VLOOKUP function into that cell: =VLOOKUP(search_key, range, index, [is_sorted])

   

4. Enter the search_key. Replace the search_key with the name of the employee you’re looking for. We’ll look for Mia in this example, so we want to enter A17 as the search key. 

   

5. Set the value range. Now we’ll replace the range with the cells that contain the data we want to search. In this case, our data is in columns A and B, so we’ll replace range with A:B.

   

6. Set the index column. Next, replace index with the column number that contains the data you want. To find the index column, count from the leftmost column. We need information from the ID number column, which is the second column from the left. So we’ll enter 2 for index. 

   

7. Determine is_sorted value. In our example, the data isn’t sorted in order, so we’ll need to use FALSE for [is_sorted]. 

   

8. Execute the function. Once you’ve entered all your inputs, hit Enter. If you did everything right, the function should return the value you were looking for. In our case, it returned Mia’s employee ID number: 123789.

   

VLOOKUP example and template

Now that you have the basics down, let’s try a more complex example. Navigate to the “Example – TRUE” tab of our demo spreadsheet to follow along. 

In this example, we have a list of employees and their salaries. These employees also receive profit sharing based on their salary, and we need to fill in that percentage. It would be really time-consuming to manually enter the profit-sharing percentage each employee receives, but you can use VLOOKUP to do it for you. Here’s how. 

1. Organize your data. The first table shows salary ranges and their corresponding profit-sharing rates. But VLOOKUP won’t be able to understand the salary range data because of the dashes and spaces. Instead, organize your salary data like the second table, with only the bottom number of the range. Use this formula as a shortcut: =LEFT(A2, SEARCH("-", A2)-1). A2 is the starting cell of the salary ranges (so you’d replace that with your own starting cell number), and you can drag the formula down until you have the lower-end number for all of your ranges.

   

2. Select an output cell. Click the cell you want your information to end up in. Here, that’s F2, where the profit sharing % for each employee begins. 

   

3. Enter the VLOOKUP function. Paste the VLOOKUP function into that cell: =VLOOKUP(search_key, range, index, [is_sorted])

   

4. Enter the search_key. We want the profit-sharing rate for John, but more specifically, for John’s salary. So we’ll replace search_key with E2. 

   

5. Set the value range. Our value range is going to be the second table we made with corresponding salaries and profit-sharing rates. So replace range with A15:B25. 

   

6. Set the index column. We want to know the profit-sharing rate, which is in column 2 of our selected table. So we’ll replace index with 2.

   

7. Determine is_sorted value. We want to replace [is_sorted] with TRUE because our data is sorted, and we’re not looking for an exact match. Entering TRUE tells Google Sheets to look for the closest match, a value that’s less than or equal to, which is what we need in this case. 

   

8. Execute the function: Hit Enter. If you’ve done everything right, Google Sheets should give you the correct profit-sharing rate, which is 1% in our case.

   

9. Drag the function. If you want to find everyone’s corresponding profit-sharing percentage, simply click on the blue dot on the bottom-right corner of the cell where you entered the function (F2) and drag it all the way down. Ignore the error codes below—everything should load correctly once you click the check mark next to the suggestion, assuming your first formula was done correctly.

   

The result leaves you with the correct profit-sharing rate for each employee without having to enter the function for each one manually.

VLOOKUP not working? Troubleshooting common errors

Is VLOOKUP not behaving like it’s supposed to? No one’s surprised. Here are a few common errors you will almost definitely encounter—and how to fix them:

• VLOOKUP returns an unexpected value: If your VLOOKUP function returns a value you weren’t looking for, double-check your formula. This can occur if [is_sorted] is set to TRUE, but the first column in the selected range isn’t sorted numerically or alphabetically in ascending order. To troubleshoot, change [is_sorted] to FALSE.

• VLOOKUP only returns the first matching value: By design, VLOOKUP always returns the first result found. If you have multiple matched search keys, you’ll need to assign them unique values so VLOOKUP can search for them properly.

• VLOOKUP with unclean data: VLOOKUP may not work properly if you’re searching for values with extra spaces or other typographical errors. Be sure to remove unwanted spaces in your sheet. You can start by going to Data > Data Cleanup > Trim whitespace.

• VLOOKUP returns #N/A error: This error can occur if you use VLOOKUP to look up a value for which no exact match exists in your first column. 

How to use VLOOKUP with multiple criteria

Let’s say you want to see how many products a customer purchased, but you have multiple customers with the same name (looking at you, Tim Johnson). Normally, the VLOOKUP function is limited to one search value, but you can scan for multiple criteria with a bit of extra legwork. Here’s how it’s done:

1. Insert a new “helper” column to the left of your lookup columns. This will be the leftmost column in your table. 

2. In the first cell of this column (A2 if your data starts in row 2), enter the formula =B2 & " " & C2. This will join the values in your existing columns and separate them with a space. 

3. Copy that formula to the rest of the cells in the helper column.

   

4. Using the standard VLOOKUP formula, place both criteria you want to search for in the lookup_value argument, separated with a space. In my example, I want to search for someone named John with the ID number 112233. Here’s the formula I used: =VLOOKUP("John 112233", A2:D22, 4, FALSE) 

5. The formula will return a value based on your search criteria (the quantity 4, in my example).

6. You’ll need to replace A2:D22 with the actual range of your table, including the helper column. The number 4 indicates that you want to return the value from the fourth column in the table. You can also adjust this as needed.

How to VLOOKUP from a different sheet

Assuming your data is spread across two sheets—say, one that holds the profit-sharing conversion and the other with your list of employees and their salaries—you’ll need to adjust your VLOOKUP formula. You can find an example of this function under the “Sheet1” and “Sheet2” tabs of our demo spreadsheet. 

In our example, if we split our data into two sheets (Sheet1 and Sheet2), we would adjust the formula like this: =VLOOKUP(B2,Sheet2!A15:B25, 2, TRUE)

This adds the worksheet name followed by an exclamation mark right before the range, which tells VLOOKUP which worksheet we want to use to pull data. 

The formula then searches for the value in B2 in the A15:B25 range on Sheet2. It then returns a match from column 2 in our designated cell on Sheet1.

More tips for using VLOOKUP in Google Sheets

If you’re like me and need someone to hold your hand through the process, here are some additional pointers for using VLOOKUP. 

Keep your data organized

Organized data reduces the chances of errors in your VLOOKUP formulas. Here are a few tips.

• Use headers. Clear, descriptive headers for your columns makes it easier to understand what each column represents. Then, when you perform a VLOOKUP function, you can quickly see where you need to pull data.

• Sort data. If you aren’t looking for an exact match, you’ll need to sort your data in ascending order so you can mark the is_sorted parameter as TRUE. 

• Eliminate empty cells. Empty cells within your dataset could lead to errors or incorrect matches.

Pre-sort the values in the leftmost column

One annoying quirk about the VLOOKUP function is that it can’t look to the left. Before you start, make sure the column that houses your search_key is the leftmost column of your range. 

Take our simple example from earlier. If we switch the order of the columns, with ID number coming before name, the function returns an error. This is because we selected B17 as our search_key and then asked it to pull data from the column to its left. 

Use INDEX/MATCH for advanced functionality

But what if moving your columns would be a giant pain? Maybe it’s a huge dataset or you have formulas in the spreadsheet that can’t be moved around.

In that case, you’ll want to ditch VLOOKUP and use INDEX/MATCH for more flexibility. That formula is: =INDEX(array or reference,MATCH(lookup_value,lookup_array,[match_type])

You can see this in action under the “INDEX/MATCH example” tab in our template. 

For our same simple example, that formula would look like =INDEX(A2:A21,MATCH(B17,B2:B21,0))

Here’s why:

1. The array or reference is the range where the data you’re looking for could be. In our case, that’s A2:A21 (the ID number column). 

2. The lookup_value is your search_key, or the value you’re looking for. That’s B17 (Mia) in our example.

3. The lookup_array is the range where your lookup_value is located. That’s B2:B21 (the name column) for us.

4. The [match_type] is 0 because we want it to return the first value corresponding to Mia. In a more complex table, you may set it at a different number if you only want it to return values after that number.

Consider wildcard characters

You can use wildcards to perform partial matches with the VLOOKUP function. Wildcards are special characters that represent unknown or variable characters in a search pattern.

For example, an asterisk (*) can represent any sequence of characters, including no characters. 

Check out an example of this under the “Wildcard * example” tab in our demo template. Suppose you have a list of ID numbers and employee names, and the first letter in each ID represents the employee department. You want to find the HR employee in the list whose ID number starts with H. You can use the * wildcard to perform this partial match.

You just need to replace your search_key with “H*”. It would look like this: =VLOOKUP("H*",A2:B10,2,FALSE)

However, if your ID numbers only contain numbers, no letters, you’ll need to use a different formula. Let’s say ID numbers start with 4. Your formula would be: =VLOOKUP("4*",ArrayFormula(TO_TEXT(A1:B10)),2,FALSE)

Use FALSE to troubleshoot

Getting error responses or incorrect results? Try using FALSE in your is_sorted parameter to get exact matches first. If that doesn’t help, then look into other reasons it didn’t work.

Make VLOOKUP case-sensitive

VLOOKUP is not case-sensitive, which means it doesn’t pay attention to the difference between lowercase and uppercase letters. If that matters in your search, you’ll need to use a separate formula: ArrayFormula(INDEX(return_range, MATCH (TRUE,EXACT(lookup_range, search_key),0)))

Use automation to do more with Google Sheets

VLOOKUP in Google Sheets is a game-changer, but there’s a lot more you can do with Google Sheets—and I don’t mean manually. With Zapier’s Google Sheets integration, you can connect Google Sheets to thousands of other apps. Learn how to automate Google Sheets, so you can spend less time learning how to do all these functions and more time on the things you’d rather be doing (probably literally anything else). Here are some worfklows to get you started:

Zapier is a no-code automation tool that lets you connect your apps into automated workflows, so that every person and every business can move forward at growth speed. Learn more about how it works.

VLOOKUP FAQ

There’s always more to learn in the always complicated world of spreadsheet functions (see Zapier’s terrifyingly detailed guides on things like building KPI dashboards, creating spreadsheet CRMs, and DIYing spreadsheet Kanban boards). For a head start, here are some quick answers to common questions. 

Can VLOOKUP return multiple values?

On its own, VLOOKUP can only return one value at a time. But you can combine VLOOKUP with other functions, like INDEX and MATCH, to return multiple values. 

What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP searches your table array vertically (the “V” in VLOOKUP stands for vertical), while HLOOKUP searches your table array horizontally (the “H” in HLOOKUP stands for horizontal).

Can a VLOOKUP look at multiple columns?

VLOOKUP can look at multiple columns, but it will always return the first match from your leftmost column. You may need to reorder your columns if you’re getting an unexpected result.

What happens when VLOOKUP finds multiple matches?

Technically, VLOOKUP can’t find multiple matches. It will only return the first exact match it finds in your table, which is why you’ll need to use unique values for each item you search for. 

What happens when VLOOKUP doesn’t find a value?

When VLOOKUP doesn’t find the value you’re searching for, it’ll return an #N/A error. Double-check that your search value doesn’t have any typos or extra spaces.

Related reading:

This article was originally published in August 2023 by Cecilia Gillen and has also had contributions from Dylan Reber. The most recent update was in September 2024.

by Zapier

You Might Also Like

21 free Google Sheets templates to boost productivity

OneDrive vs. Google Drive: Which is best? [2024]

How to automate your CRM

Streamline your meetings with AI tools and automation

The 7 best landing page builders in 2024

Automatically send personalized emails to new leads