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:
Table: You have a table with rows and columns of data.
Value: You know a specific value, like a name or ID, and you want to find more related information.
Lookup: You use VLOOKUP to look for that value in the same row but a different column.
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.
Organize your data. Enter your data into a spreadsheet or locate an existing table. (The data is already there for you in this spreadsheet.)
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.
Enter the VLOOKUP function. Enter the VLOOKUP function into that cell:
=VLOOKUP(search_key, range, index, [is_sorted])
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
A17as the search key.
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.
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
2for index.
Determine is_sorted value. In our example, the data isn't sorted in order, so we'll need to use
FALSEfor [is_sorted].
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.
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.
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.
Enter the VLOOKUP function. Paste the VLOOKUP function into that cell:
=VLOOKUP(search_key, range, index, [is_sorted])
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.
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.
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.
Determine is_sorted value. We want to replace [is_sorted] with
TRUEbecause 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.
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.
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.
Assuming your data is spread across two spreadsheets—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. Find an example of this function under "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.
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:
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).
The lookup_value is your search_key, or the value you're looking for. That's B17 (Mia) in our example.
The lookup_array is the range where your lookup_value is located. That's B2:B21 (the name column) for us.
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. 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).
Related reading:
