Skip to Content

How to Create and Use Vlookup Formulas in Google Sheets (Beginner Guide)

Posted on Last updated:

VLOOKUP is one of the most useful and versatile formulas in Google Sheets. It is a remarkable resource that allows you to quickly and easily find information in a range of cells or tables based on a specific value. 

What’s cool about using VLOOKUP formulas in Google Sheets is that it allows users to perform a variety of useful tasks in record time. From finding customer information to tracking sales data and inventory, VLOOKUP formulas in Google Sheets make your work much easier. 

However, while VLOOKUP formulas in Google Sheets is very useful, grasping it is a little tricky. But not to worry, today’s guide will bring you up to speed on how to maximize this function. 

In this beginner’s guide, we will walk you through the basics of VLOOKUP formulas in Google Sheets, including how to create the formula, how to deploy it to find information as well as how to troubleshoot common errors. 

After reading today’s Google Sheets tutorial, you should be able to use VLOOKUP quickly and seamlessly to find information in your worksheet. 

But before we delve any further, let’s start with the basics – understanding the rules of VLOOKUP formulas in Google Sheets. 

VLOOKUP Function Google Sheets – Understanding the Rules

If you’re just hearing about the VLOOKUP function, then it’s safe to say you don’t know what it means. So let’s clarify things a bit. The VLOOKUP function, or vertical lookup as it is fondly called, is a unique function that allows you to search for specific information in a range of cells. While this function makes your job incredibly easy, it has a few rules you need to know. 

Before we jump into more technical aspects of VLOOKUP formulas in Google Sheets, let’s take a few minutes to discuss some things you need to know about the VLOOKUP function in Google Sheets. 

  • The VLOOKUP function cannot be used to search for information in any given column. And that’s because the function only looks at the first column of the range. 
  • The VLOOKUP function has a parameter called “is_sorted,” which is very important to get the result you want. Also, remember that this parameter is sorted in ascending order in the first column. For context, when you indicate TRUE or omitted when the data isn’t sorted, you’ll get an error message. To this end, the is_sorted parameter must be set to False. Putting it this way will return the exact matches you’re searching for without any errors.  The reason is that VLOOKUP leverages a faster binary search algorithm that works only for sorted data. 
  • It’s important to note that the VLOOKUP function doesn’t differentiate between lowercase and uppercase characters. To put things simply, it isn’t case-sensitive. 
  • VLOOKUP can search partial matches based on wildcard characters. We will discuss more about this shortly. 

The VLOOKUP Syntax: What You Should Know

Now that you know what the VLOOKUP function is, let’s quickly help you understand the VLOOKUP Syntax. For starters, the VLOOKUP syntax contains the important parameters for the VLOOKUP formulas. Here is what it looks like. 

=VLOOKUP(search key, range, index, is-sorted)

The VLOOKUP formula contains very important parameters, including the search key, the range, the index and, of course, the is_sorted parameter. 

  • If you look at the VLOOKUP syntax above, you’ll notice that the first parameter is the search key. This parameter is super important because it is the unique identifier through which you’ll find the value you’re searching for. 
  • The next parameter in the syntax is the range. This parameter allows you to note down the columns that contain the data you’re looking for. 
  • The third parameter is the index. This lets you note the column number containing the value you want to pull. 

True or False

If you look closely at the VLOOKUP syntax, you’ll notice it has a parameter called “is_sorted.” This parameter means that the data you have in your workbook is arranged in the right order (which is ascending order)

True: If we want the data sorted, we use true. And if that’s the case, the function will return the value closest to the search key. Sometimes, it may be less or more than the value you’re searching for. 

False: If we don’t want the data to be sorted, we use False. This will typically return the value that is an exact match of what we are searching for. And if there is more than one exact value, the function will return the first of the matches. Sometimes, instead of using false, you can use 0. You’ll still get the same result. 

If you want to avoid errors, we suggest you use False or 0 for the is_sorted parameter. 

We know this sounds a bit confusing. But not to worry, you’ll get a better picture of all of this, especially when we kick off the practical aspects of this guide. For our first example, we will show you how the VLOOKUP formulas in Google Sheets works. 

VLOOKUP Formula Usage 

To help you further understand how the VLOOKUP formulas in Google Sheets work, let’s look at this very simple example. In this example, we have a phone store offering 6 different iPhones, along with their specific colors, product ID and price. So for our example, here is what our sample sheet looks like. 

Our objective with this sample is to use VLOOKUP to search for colors by iPhone type. Given our objective, it means we need to update our sample spreadsheet. We will do that by creating the header “Search Color by iPhone type. We will then use cell G5 to input the VLOOKUP formula. After typing in the VLOOKUP formula, Google Sheets will provide suggestions. 

Here is how to go about using the VLOOKUP formula to search for color by iPhone.

  • Start by filling in the suggestions shown by Google Sheets: 
  • For the search key, we will use the cell where we want to fetch the value from. We will start off with cell A2.
  • For the range, we have selected cell A2:D7 as this is where our data lies.
  • As per the index, we have chosen two. And the reason for this is that we want to pull data from the second column, i.e., Column B. And finally, for the is_sorted value, we will use 0 as our data isn’t sorted. 

If you did everything exactly as we detailed above, your spreadsheet should look something like this: 

After entering the formula, simply hit enter on your keyboard, and you should see the result generated almost instantly. 

Here is what ours looks like. 

To simplify things, we can head over to cell F5 and type in the phone type. The screenshot below shows you exactly what we mean: 

After entering a few more phone names and applying the Google autofill option, here is what your sheet should look like: 

If you haven’t used the Google Sheets autofill option before, you might want to watch this short video. It shows you exactly how to go about it. 

VLOOKUP techniques: Advanced options

Besides the basic VLOOKUP formulas in Google Sheets, there are a couple of other advanced VLOOKUP techniques that will make your job incredibly easy. 

Keep in mind that these techniques aren’t used randomly. Instead, they are for specific conditions. Plus, you can always use them to meet some specific requirements. Read on as we highlight some of these techniques. 

  • Using wildcard characters is great for performing a fuzzy search
  • You can deploy VLOOKUP to search for data in another sheet. We did a comprehensive guide on that. Click this link to learn how to do that. 
  • You can use the Google Sheets Index (Match) function for Left VLOOKUP
  • It’s also possible to use VLOOKUP to search for case-sensitive data
  • You can use VLOOKUP to compare data lists 
  • Use reverse VLOOKUP in specific scenarios
  • You can use VLOOKUP for multiple criteria. 

Leveraging Wildcard Characters for Specific VLOOKUP Requirements

Unlike executing an exact search using VLOOKUP, the wildcard search typically displays data that doesn’t exactly match your lookup value. To search for data in your worksheet using the wildcard search option, you can use the wildcard search characters: the asterisk (*) and the question mark (?). Read on as we break down how these wildcard characters work. 

Asterisk

Anytime you add an asterisk to your VLOOKUP formula, you will be able to search for a value related to an item. What the asterisk does is search for a sequence of characters instead of its full name. 

Here is the VLOOKUP syntax that includes the parameter for wildcard: 

=VLOOKUP(search_key&”*”,range,index,is_sorted)

We will update the earlier spreadsheet we used to demonstrate how this works. For this example, we will have a heading for search price by type. Here is what that looks like. 

Note: If you look at the above screenshot, you’ll notice we have added a heading to search for price by phone type. For this example, our search key is cell G2 (which is where we have entered the search term iPhone 8. 

Now to search for price, we will head over to cell G3, where we want the result and type in our VLOOKUP formula with the parameter for the wildcard. 

=VLOOKUP($G$2&”*”,A2:D7,4,0)

Once you have done that, hit the Enter button on your keyboard and wait a few seconds for Google Sheets to generate the result. Here is the result we got after executing the steps detailed above. 

By simply adding an asterisk to our formula, we no longer need to search for iPhone 8. All we need to do is search for a sequence of characters similar to it, and our VLOOKUP formula will search for the corresponding value. 

Question mark

Whenever you add the “?” mark before the search key in your VLOOKUP formula, what it does is replace the starting character. 

Here is what the VLOOKUP syntax looks like with the “?” mark included. 

=VLOOKUP(“?”&search_key,range,index,is_sorted)

By adding the question mark before the search key, we can now type in “Phone 8” in cell G2, and Google Sheets will replace the character and give us the corresponding value. 

Another thing we can do is add the “?” mark after the search key. Doing this will replace the ending character. 

Here is what the VLOOKUP syntax looks like with the “?” mark added after the search key: 

=VLOOKUP(search_key&”?”,range,index,is_sorted)

By adding a question mark after the search key, we can go ahead and type in “iPhone”, and Google Sheets will automatically replace the character and generate the corresponding value. 

How to VLOOKUP from a different sheet

Even though VLOOKUP from a different sheet is easy, it is a bit tricky. Read on as we break things down. 

To VLOOKUP from a different sheet, you need to use the below formula: 

=VLOOKUP(search_key,’Sheet Name’!range,index,is_sorted)

Keep in mind that for this formula to work, we need to press the F4 key after selecting the cell range we would like to lockdown. 

Since we want to VLOOKUP from another sheet, we need to update our current spreadsheet to include a column for stock. After which, we will create another different sheet, which we will name Catalogue. This sheet will include a column for product ID and another for stock. 

Here is our updated worksheet: 

In the screenshot below, you’ll notice we have the stock for each item in a separate sheet named Catalogue. To seamlessly import the stock into our main workbook, we will use the VLOOKUP formula below. 

=VLOOKUP(C2,Catalogue!$A$2:$B$7,2,false)

  • So head over to your main worksheet and select cell E2, which is where we want our VLOOKUP result to show up. 
  • After selecting the cell, type in the following formula:

=VLOOKUP(C2,Catalogue!A2:B7,2,false)

  • Once you’re done typing in the formula, hit the Enter button on your keyboard . The data in the catalogue sheet should now be imported into your main worksheet. Here is what ours looks like: 

Looking carefully at the screenshot above, particularly in cell E2, you’ll notice the result imported from our Catalogue sheet. 

Now, we can simply use the Google Sheets autofill option to generate the result for the other cells. The video below shows you how to go about that. 

Still confused about how to VLOOKUP from a different sheet? Well, let’s break down the formula to clarify things better. 

  • We used C2 as our search key. The reason was that we wanted to choose the item ID as our search key. 
  • From our example, we selected the cell range “A2:B7.” We did that because that’s where the data we want lies in the Catalogue sheet. 
  • We selected 2 as our index because we wanted to pull the stock information, which is housed within the second column in the catalogue sheet. 
  • Finally, we entered false for the is_sorted parameter. We did so to avoid any sorting-related errors. 

Google Sheets Index Match formula for left Vlookup

By default, VLOOKUP formulas in Google Sheets is designed to search on the right. But guess what? You can search for data on the left using the following formula. 

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

For this example, we want to search for color by item ID. So we will use the following sample data: 

  • To start, select the cell where you’d like the result generated. For us, we will use cell G6. 
  • Now, head over to the formula bar and type in the following formula: 

=INDEX(B2:B7,MATCH(F6,C2:C7,0))

  • After entering your formula, hit the Enter button on your keyboard, and the result should be generated almost instantly. Here is what ours looks like.

Performing Case-sensitive VLOOKUP in Google Sheets

Do you know you can use VLOOKUP formulas in Google Sheets for case-sensitive data? Oh yes, and the process is as straightforward as other VLOOKUP processes. To search for case-sensitive data using Google Sheets VLOOKUP, we will tweak the earlier example we used. So instead of searching color by item ID, we will use search phone type. 

So here is what our sample spreadsheet should look like. 

If you look closely at the screenshot above, you’ll notice we have updated the spreadsheet. In particular, we added another phone type to the list. We named this one “Iphone 11.” Although our worksheet already has a phone type named “iPhone 11,” which starts with a lowercase “i”, the new one starts with an uppercase “I.”

For case-sensitive VLOOKUP in Google Sheets, you’ll need to use the following formula.

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

What the formula does is search for an exact match since it will factor in the letter case: 

Here is how to perform case-sensitive VLOOKUP in Google Sheets. 

  • Start by selecting the cell where you want the result generated. For our example, we will use cell G6. 
  • Now, navigate to the formula bar and type in the following formula: 

=ARRAYFORMULA(INDEX(B2:B8, MATCH(TRUE,EXACT(A2:A8,F7),0))

  • Finally, hit Enter on your keyboard. The result should be generated almost instantly in your chosen cell. Here is what ours looks like. 

By leveraging the formula above, we were able to search for “Iphone 11.” Google Sheets understood our instructions and pulled the correct value. 

Using The VLOOKUP Function in Google Sheets To Compare Data Lists

In addition to everything we have covered so far, let us quickly add that you can compare data within the same or entirely different sheets using VLOOKUP formulas in Google Sheets. While you’ll still use the basic VLOOKUP formulas in Google Sheets, you still have to follow some specific scenarios. 

  • First, you must set the search_key as the cell containing the information you’d like to compare. 
  • Secondly, you must set the range of the data you want to compare with.

When comparing data within the same sheet, it’s important to use the following formula. 

=VLOOKUP(search_key,range,index,is_sorted)

When comparing data from a different sheet, here is the formula you want to use. 

=VLOOKUP(search_key,’Sheet Name’!range,index,is_sorted)

Alternative functions like VLOOKUP

Google Sheets has a plethora of other functions that work pretty similarly to the VLOOKUP function. Here, check out a couple of them: 

  • HLOOKUP: This powerful function in Google Sheets is used to perform a horizontal lookup.
  • INDEX MATCH: This function works very similarly to the VLOOKUP function. What we love most about this function is that it can perform a left lookup. The function combines both the INDEX function and the MATCH function. 
  • XLOOKUP: This function is important for looking up a match based on the position of the search key. 

Final Thoughts

VLOOKUP formulas in Google Sheets is an important functionality you should know and use, especially if you work with a lot of data and need to pull data across different sheets. In today’s guide, we covered everything you need to know about how to create and use VLOOKUP formulas in Google Sheets. We started from the basics and went all the way. 

If you have always had challenges with VLOOKUP formulas in Google Sheets, we are sure today’s Google Sheets tutorial has made your work easy. We included several examples and added some screenshots and video resources to make it a tad easy to understand. 

After reading today’s guide, you can take some time to practice. We are sure you’ll get the hang of how it works. 

In case you need further clarification, feel free to leave a comment below, and we will be happy to respond with some guidance.