Grasping the VLOOKUP Google Sheets function was a little tricky for me 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.
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.
Table of Contents
Download Our Example Spreadsheet
To make the learning process easier for you, here’s our EXAMPLE SPREADSHEET that you can make a copy of and use to follow along with the guide.
Rules of VLOOKUP Function Google Sheets
So, what is VLOOKUP in Google Sheets? VLOOKUP, or vertical lookup, searches for specific information in a cell range but has a few rules.
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.
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 that 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 matches based on the wildcard characters. I discuss these characters below.
VLOOKUP Syntax Google Sheets
So what is the VLOOKUP syntax in Google Sheets?
=VLOOKUP(search key, range, index, is-sorted)
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.
True or False
The last parameter is “is_sorted”. This value is optional. Essentially, this means that the data you have is arranged in the right order (which is the ascending).
True= We use true if we want the data to be sorted. In this case, the function will return the value closest to the search key which may be less than or more than the value we’re searching for.
False= We use False when we don’t want to sort the data. This will return the value that is an exact match to the value we’re searching for. If there’s more than one exact value, the formula will return the first of the matches. You can also use 0 instead of False for the same results.
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 Google Sheets formula to give you an idea of how it all normally unfolds.
Usage
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.
- ‡ZaraThen, in the search key (Cell F5), I typed in Dell PC, which is a “PC Type”, and the corresponding color showed 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.
For example:
- 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:
Asterisk
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.
=VLOOKUP(search_key&"*",range,index,is_sorted)
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.
Question Mark
If you add the “?” before your search key, it will replace the starting character.
=VLOOKUP("?"&search_key,range,index,is_sorted)
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.
=VLOOKUP(search_key&"?",range,index,is_sorted)
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:
=VLOOKUP(search_key,'Sheet Name'!range,index,is_sorted)
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 sheets formula:
=VLOOKUP(C2,Lookup!A3:B8,2,false)
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 Google Sheet Vlookup, let’s take the above vlookup Google Sheets 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 in Google Sheets. Essentially, you will use the standard VLOOKUP formula Google Sheets, 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.
Same sheet
- To compare data within the same sheet, just use the formula
=VLOOKUP(search_key,range,index,is_sorted)
Different sheet
- To compare data from a different sheet, use the formula
=VLOOKUP(search_key,'Sheet Name'!range,index,is_sorted)
Reverse VLOOKUP
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:
=VLOOKUP(search_key,{search_range,lookup_range},2,0)
Below, I have used this formula in the context of my data like this:
=VLOOKUP(F5,{C:C,A:A},2,0)
By using the formula above, I could search for the PC Type by entering the Item ID.
How to use Nested Function in VLOOKUP
Nesting is when you use a function inside another function to make it more effective or to combine their uses. You can use the VLOOKUP function with nested formulas in Google Sheets.
For example, in our sample spreadsheet we can find the laptop with the highest price by nesting the Max function inside the VLOOKUP formula.
Here’s how:
- Click in the cell you want to return the results
- Type the VLOOKUP formula and choose it from the suggestions.
- Type the Max formula.
- Select the column with the prices.
- Close the brackets and add a comma then select the range of the lookup.
- Add the index column and 0 for an exact match.
- Close the brackets and click enter.
The nested Max formula finds the largest number in the search range and the VLOOKUP returns the matching data in the cell corresponding to that number in the 2nd column of the specified range.
Functions Like VLOOKUP
There are a couple of other functions in Google Sheets that work similarly to VLOOKUP including:
- HLOOKUP – This function is used to perform a horizontal lookup
- INDEX MATCH – This function is an alternative to the VLOOKUP that can perform a left lookup. It is a combination of the INDEX function and the MATCH function.
- XLOOKUP – This function is used to look up a match based on the position of the search key. It usually defaults to the exact match unless it can’t be found in which case it will return the closest match.
Conclusion
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.
Wrapping Up
If you found this VLOOKUP Google Sheets guide useful, check out our related content below. Alternatively, you may want to take a look at our comprehensive Google Sheets and Google Forms course.
ACCESS GOOGLE FORMS & SHEETS MASTERCLASS
Related:
-
- 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
- How to Calculate Percentage in Google Sheets
