VLOOKUP is a helpful tool in Google Sheets that allows you to find information from one table and use it in another. It is called “VLOOKUP” because it looks for values vertically.
Imagine you have a spreadsheet with a lot of data and want to find a specific piece of information quickly. The VLOOKUP function can help you with that.
Let’s say you have a list of products with their prices, and you want to find the price of a particular product. You can use VLOOKUP to search for the product name in one column and retrieve its price from another.
If you’re new to VLOOKUP, it might seem complicated at first. But don’t worry! In this guide, we’ll break down everything you need to know about the VLOOKUP function and make it easier to understand.
To clarify things, we’ll provide several VLOOKUP examples in Google Sheets. These examples will help you grasp the concept better and apply it in different situations.
But before we show you any VLOOKUP examples in Google Sheets, let’s start with the basics, understanding the VLOOKUP syntax.
VLOOKUP Function Syntax
Before we dive into VLOOKUP examples in Google Sheets, let’s understand how the VLOOKUP function works. If you’re new to VLOOKUP, here’s what the VLOOKUP syntax looks like:
=VLOOKUP(search_key, range, index, [is_sorted])
VLOOKUP stands for Vertical Lookup, meaning it searches for a value up and down a column.
It’s called “vertical” because it looks for values vertically rather than horizontally. There’s also a less commonly used function called HLOOKUP for horizontal lookups.
Now, let’s take a closer look at the parameters that make up the VLOOKUP function syntax.
- Search_key: If you examine the VLOOKUP syntax above, you’ll see that the first parameter is the search_key.
The search_key is the item you want to find in the lookup table.
Simply put, It is the value you’re searching for in the original table, and you want to locate its corresponding information in the lookup table.
- Range: The second parameter is the range. It represents the table we are going to search in. And just so you know, VLOOKUP always searches down the first column of its table.
- Index: If the search term is found, the value from a specific cell is returned. What the index value does is determine which column of the lookup table to use.
If, for instance, the index number is greater than the number of columns in the table (i.e., you’re requesting the VLOOKUP formula to return values from column 4 of a lookup table with only three columns), you’ll typically get an #REF! Error.
- Is_sorted: This is the last parameter in the VLOOKUP syntax. It takes either a TRUE or FALSE value. It can sometimes be written as 1, which represents (True) or 0, which represents false.
Here is what you need to know about the False configuration.
When the VLOOKUP function has False included, it means:
- There is no exact match
- It isn’t case sensitive
- When there are multiple matches, the first match is found, and the returned value is taken from that row.
- In a scenario where no lookup value is found, applying the VLOOKUP formula will return the #N/A error message.
- The FASLE configuration is used for nearly all VLOOKUP cases.
When the VLOOKUP function has TRUE included,
- It is meant to find the nearest match that is either less than or equal to the search key.
- As reiterated earlier, this option is rarely used.
Practical VLOOKUP examples in Google Sheets
Now that you understand what the VLOOKUP syntax looks like and its related parameters let’s quickly go over some VLOOKUP examples in Google Sheets to broaden your understanding.
We will start off with a very simple VLOOKUP example in Google Sheets.
Here is the sample data we will use for this example:
Our objective with this simple VLOOKUP example is to search for Ada Hawkins in column 1 and return the value from the 4th column in our lookup table.
Here is how to go about it:
Step 1: Choose the cell where you want the result generated
The first thing we need to do is choose the cell where we want the lookup result generated. For our example, we will use cell G3. So go ahead and select that cell in your spreadsheet.
Step 2: Enter the VLOOKUP formula
Having chosen the cell where we want our lookup result generated, head over to the formula tab and type in the following formula:
=VLOOKUP(F3, A2:D5,4)
Step 3: Hit Enter
Done typing in your VLOOKUP formula? Cool. Now, hit the Enter button on your keyboard. The lookup result should be generated in our selected cell.
If you did everything exactly how we showed you, you should have something like this:
Now, that’s how to perform a simple VLOOKUP operation. In our next VLOOKUP examples in Google Sheets, we will take things up a notch and show you more complex VLOOKUP operations.
For our next example, we will show you how to use the VLOOKUP function to join tables in Google Sheets.
Read Also: Vlookup Examples in Excel
How to use the VLOOKUP function to join tables in Google Sheets.
As you may already know, you can use the VLOOKUP function in Google Sheets to bring data from one data table and add it to another.
Having established that premise, let’s show you how to quickly use the VLOOKUP function to join tables in Google Sheets.
Let’s assume we run an ad agency for top-tier clients who pay a monthly fee for our service. And we have their data in a Google Sheets that looks like this:
Let’s also say we have a second table that contains our customers’ location details, as shown in the Google Sheets below:
To have a thorough understanding of our sales data, what we want to do with this VLOOKUP examples in Google Sheets is to combine these tables so we can clearly see revenue by location.
Now that we know exactly what we are after, here is how to do it.
Step 1: Update our spreadsheet to include a column for the location
Before we can combine both of these tables to show revenue, we need to create a column for revenue. So go over to your original spreadsheet and create a column for that:
Step 2: Choose the cell where you want the result generated
Having updated our spreadsheet to include a column for location, we now need to select the cell where we want our lookup result to be generated. Since we are using column 6 for that, we will go ahead and select cell F4.
Step 3: Type in the VLOOKUP formula
With the cell where we want to generate our lookup result selected, we need to enter our formula, which is easy. Simply navigate to the formula bar and type in the following formula:
=VLOOKUP( A4,$I$3:$J$7,2, false)
Note: If you look at the VLOOKUP formula we used in this example, you’ll notice it has a few $ signs around the table range reference. The reason we included that is to make an absolute reference. Not just that, including the $ sign helps lock the reference range.
We recommend doing this so that the lookup table doesn’t change when you copy the formula.
Instead of adding the $ sign manually, you can simply use the F4 key to add or remove the dollar signs quickly.
Step 4: Hit the enter button
Now that we have typed our VLOOKUP formula in the formula bar, we are almost done. All that is remaining to do is hit the Enter button.
If you did exactly as we showed, the lookup result should be generated in your preferred cell.
Here is what ours looks like:
From the screenshot above, you’ll notice that we only generated the result of one client. So we need to do the same for the other clients. But instead of doing that manually, we can save time using Google Sheets auto-fill option.
If you don’t know how to do that, the video below will bring you up to speed.
That wasn’t too hard, was it?
How to use the VLOOKUP function to compare data lists in Google Sheets
We are sure some of the VLOOKUP examples in Google Sheets we have covered so far have provided some insight into how the VLOOKUP function works; now, let’s look at something different.
In this VLOOKUP examples in Google Sheets, we want to use the VLOOKUP function to compare data lists in Google Sheets.
For our example, we will have two lists. Our objective is to compare the names in List 1 and List two to see where these names overlap.
Here is the sample data we will use for this example:
Now that we have the sample data we would like to use for this example, let’s quickly go over the VLOOKUP formula to compare the data list in Google Sheets.
Here is what it looks like
=IFERROR( VLOOKUP( A2, D:D, 1, false ), “Not in List 1” )
Before we deploy this formula for this example, here are a few things you should know.
- Our lookup table is in a single column D:D. To this end, the index must be 1
- Having the index this way returns the name if found.
- If you look at the formula, you’ll notice that we included the IFERROR function. The reason we did so is to show a custom error message when the names are not found.
Having explained the formula, let’s jump right into the practical aspect of this example.
Step 1: Choose the cell where you want the result displayed
First things first, select the cell where you’d like your result displayed. For the purpose of this example, we will use E2. So go ahead and select that cell.
Step 2: Enter the VLOOKUP formula
With the cell where you want the lookup result generated, navigate to the formula bar and enter the following VLOOKUP password:
Step 3: Hit the enter button
If you enter the formula exactly as we showed you, the only thing left to do is hit the Enter button on your keyboard. With that done, Google Sheets will automatically generate the lookup result in your selected cell.
Our looks something like this:
From the image above, you can see that we only got the result for one name. To get results for the other names, we need to do the same process. This will help us identify the names that are not in List 2.
But doing this manually will take a lot of time and effort. Luckily, we can use a helpful feature in Google Sheets called auto-fill. This feature makes our job easier by automatically filling in the formulas for the remaining names.
Here is a video showing you how to deploy Google’s Sheet auto-fill feature to generate results for the other cell:
If you watch the video above, you’ll see that we compared names in List 1 with names in List 2 quickly without spending a lot of time. We used a simple formula for this. We also used the IFERROR function to highlight names not in List 1.
Looking closely, you’ll notice that some cells have the message “not in List 1” recorded.
That’s how you can compare data lists in Google Sheets using the VLOOKUP function.
But we’re not finished yet.
So far, we have compared names in List 1 to those in List 2. Now, we’ll repeat the same process, but this time we’ll compare names in List 2 to names in List 1.
Even though we will use the same formula, we will tweak things to reflect exactly what we want.
For this other section, we will be using the following VLOOKUP function.
=IFERROR( VLOOKUP( D2 , A:A , 1 , false ) , “Not in List 1” )
Now, let’s show you how to go about it.
Step 1: Choose the cell where you want your result generated
As we did for the first section, you must choose the cell where you want the result generated. For this example, we will select cell B2.
Step 2: Enter the VLOOKUP formula
Now, head over to the formula bar and type in the following formula:
=IFERROR( VLOOKUP( A2 , D:D , 1 , false ) , “Not in List 2” )
Step 3: Hit Enter
After typing your formula, all that is left to do is hit the Enter button. With this done, Google Sheets will automatically generate the result in the selected cell.
If you followed the steps exactly as we showed you, your spreadsheet should look something like this:
From the screenshot above, you’ll notice that we only generated the result for the first name. So let’s do the same for the other cells in column B.
However, doing it manually will waste our time, so let’s use Google Sheets auto-fill feature to make it easy.
Although we have earlier shown you how to use Google Sheets auto-fill option, we have another video for this particular section. Check it out below
The video above not only displays a list of names that are present in both List 1 and List 2, but it also shows you the names that are not in List 1.
So that’s how you can compare data lists in Google Sheets using the VLOOKUP function.
Please note that the VLOOKUP examples in Google Sheets we covered in today’s tutorials are just a small part of what you can do with the VLOOKUP function. As you become more proficient, you’ll be able to use the VLOOKUP function for more complex projects in Google Sheets.
Final Thoughts
When you first start using the VLOOKUP function, it’s normal to feel overwhelmed and intimidated. However, as you become familiar with how it works, you’ll realize how easy it can make your work.
Whether you’re using the VLOOKUP function for the first time or have been using it in your Google Sheets projects for a while, our comprehensive guide on VLOOKUP examples in Google Sheets is here to help you. We’ve provided step-by-step processes, along with screenshots and video illustrations, to make it easier for you to understand.
Feel free to utilize these resources to simplify your tasks. If you encounter any issues or have questions about the steps we explained in today’s guide, leave a comment, and we’ll be happy to assist you.
We hope this guide has been helpful to you.