My biggest pet peeve when working on a project is scrolling through huge sets of data to find specific information. I believe it takes up so much of my valuable time that I can productively spend elsewhere.
Due to this particular reason, VLOOKUP on Google Sheets is one of my favorite functions.
So, what is VLOOKUP in Google Sheets? With the VLOOKUP function, Google Sheets allows anyone to look up data in a table arranged vertically. It is an essential searching function to use with large sets of data.
The VLOOKUP function has been a continuous part of my work life as it helps make my workflow a breeze. I have to admit that grasping it was a little tricky at first. I imagine it will be the same for you. But you don’t have to worry; it will only take a little reading and practice to get going.
Rest assured, the little effort you put in will be worth it, and you’ll see what I mean when I say it is one of the most useful functions of Google Sheets.
This article will demonstrate how to use VLOOKUP in Google Sheets. I’ll provide a Google spreadsheet VLOOKUP example and detail everything you need to know, including syntax and usage.
Rules of VLOOKUP Function Google Sheets
VLOOKUP, or vertical lookup, searches for specific information in a cell range but has a few rules.
Before moving into more complex aspects of VLOOKUP, let’s discuss some things you need to know about VLOOKUP in Google Sheets.
- VLOOKUP functionality cannot be used to search for information in any given column. It always looks at the first column of the range.
- The “is_sorted” parameter means if the data is sorted in ascending order in the first column. An error will show if you indicate TRUE or omitted when the data is not sorted. The “is_sorted” parameter must be FALSE to return exact matches and not display any errors. This is because the VLOOKUP function uses a faster binary search algorithm that is only for sorted data.
- VLOOKUP does not differentiate between lowercase and uppercase characters. In other words, it is not case-insensitive.
- VLOOKUP can search with partial match based on the wildcard characters. I discuss these characters below.
So what is the VLOOKUP syntax in Google Sheets?
In short, it contains parameters that include the search key, the range, the index, and whether the data is sorted or not.
The first parameter is the search key, which is required because it is the unique identifier by which you’ll find the value you’re searching for.
The second necessary parameter is the range. This is where you will note down the columns that contain the data you’re looking up.
The third parameter is the index. This parameter is where you will note down the column number containing the value you want to pull.
The last parameter is “is_sorted”. This value is optional. Essentially, this means if the data you have is arranged in the right order (which is the ascending). To avoid errors, you should note down “False” or “0” in this parameter.
Does this sound a little confusing? Don’t worry, it will only get clearer as you carry on reading this article. Next, I’ll share an example application of the VLOOKUP formula to give you an idea of how it all normally unfolds.
To break it down into steps, let’s take a look at a VLOOKUP Google spreadsheet example. In this example, we have a computer store with 6 different “PC Types” and their corresponding color, item ID, and price.
Let’s use VLOOKUP to search for Color by PC Type:
- I have created a table with the header “Search Color by PC Type” and in cell G5 of this table, I will type in the VLOOKUP formula. Once I have typed in the VLOOKUP formula, Google Sheets will show a suggestion.
- Next, fill in the details following the suggestion shown by Google Sheets:
- For the search key, I have used cell F5 since this is where I will type in the “PC Type”.
- For the range, I selected cell A2:D6 since this is where my data lies.
- For the index, I have entered 2, since I am pulling the data from the second column (Column B), and my “is_sorted” value is 0 since the data isn’t sorted.
- Then, in the search key (Cell F5), I typed in Dell PC, which is a “PC Type”, and the corresponding color has shown up.
- After entering a few more PC Types and dragging the formula down into the rest of the cells, here’s what shows up.
Additional VLOOKUP Techniques
Apart from the basic VLOOKUP methods, there are a few other advanced VLOOKUP techniques that can help you immensely.
These techniques are for specific conditions, and you can use them to meet your requirements.
- Using Wildcard Characters for a fuzzy search
- Using VLOOKUP to search data from another sheet
- Using the Google Sheets Index(Match) formula for Left VLOOKUP
- Using VLOOKUP to search for case-sensitive data
- Using VLOOKUP to compare data lists.
- Using Reverse VLOOKUP
- Using VLOOKUP for Multiple Criteria
Using Wildcard Characters
Unlike an exact search, the wildcard search will display data that does not exactly match your lookup value. You can use two wildcard search characters to search for data in your sheet, the asterisk (*) and the question Mark (?).
Here’s how they work:
If you add an asterisk to your formula, you can search for a value related to an item by searching for a sequence of characters instead of its full name.
Note: In this screenshot, I have created a table to search for price by PC Type. My search key is cell H2 (where I have entered the search term “Leno”), and the price has shown up as 400 USD.
By adding an asterisk to my formula, I no longer need to search for “Lenovo PC”. I can simply search for a sequence of characters similar to it and VLOOKUP Google Sheets will pull the corresponding value.
If you add the “?” before your search key, it will replace the starting character.
By adding a question mark before the search key, I can now type in a “ell PC”, and Google Sheets will replace the first character and pull the corresponding value.
If you add the “?” after your search key, it will replace the ending character.
By adding a question mark after the search key, I can now type in a “Dell P”, and Google Sheets will replace the last character and pull the corresponding value.
VLOOKUP From a Different Sheet
VLOOKUP from a different sheet is pretty simple. All you have to do is use the formula:
Also, for this formula to work, you must press the F4 key after selecting your cell range to lock it down (marked by the $ sign).
In the screenshot below, I have the stock for each Item ID in a separate sheet named “Lookup”. To import the stock into my main sheet, I used the VLOOKUP formula:
Then, I pressed F4 after selecting the range “A3:B8” and it turned into “$A$3:$B$8”.
Finally, I clicked enter and the data from the “Lookup” sheet was imported into my main sheet.
Here’s a breakdown of the formula to help you understand:
- I used C2 as the search key because I wanted to choose the Item ID as the search key.
- I selected the cell range “A3:B8” because that is where the data lies in the “Lookup” sheet.
- I entered the index as 2 since I wanted to pull the stock, which was in the second column (column B) of the “Lookup” sheet.
- I used entered false in “is_sorted” to avoid a sorting-related error.
Google Sheets Index Match formula for left Vlookup
Vlookup is designed to search on the right. But, you can search for data on the left using the following formula:
=INDEX (return_range, MATCH(search_key, lookup_range, 0))
In the screenshot below, I used the Item ID to search for its corresponding color, which was on the left using the index match formula.
Case-Sensitive Vlookup in Google Sheets
To search for case-sensitive data using Vlookup, let’s take the above example and search by PC Type instead of Item ID.
I have added another PC type to the list; this one is named “apple PC”. Although the sheet already contains a PC Type named “Apple PC” starting with an uppercase “A”, this new one starts with a lowercase “a”.
So, by using this formula, you can search for an exact match since it will consider the letter case:
=ArrayFormula(INDEX(return_range, MATCH (TRUE,EXACT(lookup_range, search_key),0))
By using the above formula, I was able to search for “apple PC” and Google Sheets pulled the correct value because the search became case-sensitive.
Using The VLOOKUP Function in Google Sheets To Compare Data Lists
In addition, you can compare data within the same sheet or a different sheet using VLOOKUP. Essentially, you will use the standard VLOOKUP formula, but you must follow these criteria:
- Set the search_key as the cell containing data you want to compare.
- Set the range to the data you want to compare it with.
- To compare data within the same sheet, just use the formula =VLOOKUP(search_key,range,index,is_sorted)
- To compare data from a different sheet, use the formula =VLOOKUP(search_key,’Sheet Name’!range,index,is_sorted)
In addition to the Index(Match) formula, you can use Reverse Vlookup to search for data on the left. This is by far the most straightforward approach to left VLOOKUP. All you have to do is use this simple formula:
Below, I have used this formula in the context of my data like this:
By using the formula above, I could search for the PC Type by entering the Item ID.
As detailed in this article, VLOOKUP is an amazing functionality to know and use. I’ve tried to add everything you need to know about this functionality from its syntax, rules, and along with a step-by-step case of how it all unfolds.
After reading this article, all that’s left for you to do is practice, and you’re all set!
Frequently Asked Questions
How do I VLOOKUP an entire column in Google Sheets?
Using the basic VLOOKUP formula (=VLOOKUP(search_key, range, index, is_sorted) you can search for the corresponding values of data in an entire column. All you have to do is write up the basic VLOOKUP formula for the first item in the column and then drag the formula down to all other columns.
How do I do a VLOOKUP and if in Google Sheets?
You can use a combination of VLOOKUP and IF function in Google Sheets by using this formula:
=VLOOKUP(search_key, IF(logical_expression, value_if_true, value_if_false), index, is_sorted)
In addition, you can use a combination of IFERROR and VLOOKUP to show a custom message explaining the error instead of a default error code.
How do I VLOOKUP two columns in Google Sheets?
You can VLOOKUP for Multiple Criteria if you have to VLOOKUP two columns in Google Sheets to retrieve their corresponding value. There are various other cases where you may need to VLOOKUP Multiple Criteria, which are slightly more complex than basic VLOOKUP.
How do I do a VLOOKUP with multiple values?
If you’re wondering how to do a VLOOKUP in Google Sheets with multiple values, it’s simple. You can either use multiple search keys or multiple index columns to do a Google Sheets VLOOKUP with multiple values.
- How to VLOOKUP Multiple Criteria in Google Sheets (Easy Steps)
- How to VLOOKUP From Another Sheet on Google Sheets
- XLOOKUP in Google Sheets: 3 Best Alternatives to Try
- How to Use LOOKUP in Google Sheets (Easy Tutorial)
- How to Get the Last Value in a Column in Google Sheets (Lookup Formula)
- The 3 Google Sheets Wildcards and How to Use Them
- Top 13 Best Google Sheets Courses Online