In this article, I’ll share all the necessary tips and tricks to help you master the function Vlookup in Google Sheets. Whether you’re a beginner or an advanced user, these tips and tricks will take your skills to the next level.
Function Syntax and Arguments
Syntax
VLOOKUP(search_key, range, index, [is_sorted])
Arguments Explained
search_key– The value (text, number, date, DateTime, etc.) to search for.- The search_key must be from the first column of the range.
range– The range/array to consider for the search.- Only the first column in the range is searched for the search_key specified.
- For other columns to search, there is a workaround included in this Sheets Vlookup tutorial.
index– The column number of the value to be returned. The first column in the range is numbered 1.is_sorted– It’s an optional argument and TRUE by default, which indicates whether the column to be searched (the first column of the specified range) is sorted.- FALSE (unsorted) is recommended in most cases.
- When is_sorted is set to FALSE, it returns an exact match. If there are multiple matches, the value from the first found row is returned. If there’s no match, Sheets Vlookup will return the #N/A error, which you can make blank by simply wrapping Vlookup with the IFNA function.
- When is_sorted is set to TRUE, it returns the nearest match. It means less than or equal to the search_key. If all values in the search column (the first column in the range) are greater than the search key, #N/A is returned. Here, too, you can use IFNA to return a blank.
- FALSE (unsorted) is recommended in most cases.
Are you switching from Excel to Sheets? Then don’t forget to check out this comparison of Sheets Vlookup with Excel Vlookup – Comparison of Vlookup Formula in Excel and Google Sheets.
A to Z to Vlookup in Google Sheets
Let’s dive into all the possible variations of the Vlookup formula in this comprehensive tutorial.
Basic Example Formula
If you’re a newbie, this is the perfect place to start!
Vlookup in Google Sheets is one of the first functions you should learn to become a pro Sheets user.
Purpose of Vlookup in Spreadsheet Applications
You can use this function to search vertically in the first column of a table or data range with a search_key and return the answer from a column that you specify. Let me explain with an example:
Let’s say you want to find the mark for “Test 2” for “Student 3”. In this case, you can use “Student 3” as the Vlookup search_key, as it’s in the first column. The index of “Test 2” is 3.
Using Vlookup, we can extract the mark 86. How? By using “Student 3” as the search key in Vlookup in Google Sheets and specifying the column number (index) as 3.
Basic Vlookup Formula Example:
=vlookup("Student 3",A2:C5,3,FALSE)
This formula will return the mark 86.
Don’t forget to specify the last argument as FALSE, which indicates that the range is not sorted and an exact match is required.
If your range is sorted, you can use TRUE or skip the last argument. However, it’s best to always use FALSE. Let’s see one more basic example of the Vlookup function in Google Sheets.
The formula above searches down the first column for the search key “Banana” (in cell D2) and returns its “Price” from Column 2.
Search Keys from a Range – Vlookup Advanced Tips #1
Sheets Vlookup allows for multiple search keys. For example, let’s say you want to look up the prices of “Apple” and “Mango” in the example below. How do you do that?
You can use a search key range reference and then wrap the formula with the ArrayFormula function as shown below.
If you want to specify multiple search keys within the Sheets Vlookup formula, enclose the multiple search keys within Curly Brackets.
=ArrayFormula(vlookup({"Mango";"Apple"},A2:B5,2,FALSE))
Return an Entire Row – Vlookup Advanced Tips #2
Contrary to popular belief, you can use the Vlookup function to search down the first column using a search key and return an entire row value of the matching cell. How?
Example Vlookup Formula that Returns an Entire Row:
Simply enter all the column numbers separated by commas and enclose them in Curly Brackets. Finally, wrap the entire formula with ArrayFormula.
You can make this Vlookup formula more flexible. The previous formula would break if you insert a new column or columns between columns A and D.
This Google Sheets Vlookup formula can solve this. It’s fully flexible. The {1,2,3,4} in the previous formula is replaced by the COLUMN(A2:D5) formula.
=ArrayFormula(vlookup("Student 2",A2:E5,COLUMN(A2:D5),FALSE))
The COLUMN formula returns the number 1, 2, 3, 4 (A, B, C, D).
Variable in Column Index – Vlookup Advanced Tips #3
You can move the column index along with the column. For example, let’s say you want to return the value from column 4. But if you insert a new column, this column number may become 5.
How do you make the Vlookup index column number automatically move as per the new range? Let’s see how to get the sticky column Index in Google Sheets Vlookup.
To get a sticky column index in Sheets Vlookup, use the COLUMNS function, not the COLUMN function, in the Index.
The COLUMNS function counts the columns in the range A2:D2 and returns 4.
If you want the third column value, the formula would be as follows:
=vlookup("Student 2",A2:D5,COLUMNS(A2:D2)-1,FALSE)
Leftward Vlookup – Vlookup Advanced Tips #4
The search key in Sheets Vlookup must always be from the first column. However, there is a workaround. What if my Vlookup search key is in the fourth column? Here’s the solution:
Steps to Follow:
- Rearrange the column positions using Curly Brackets.
- Assume your original range is A2:D4, and your search key is “Student 3” from column 4. So, bring that fourth column to the first column. Only then will the Vlookup work.
- You can rearrange the column positions within the Vlookup in Google Sheets itself using the following format:
{D2:D5,A2:C5} - Now, see the Vlookup formula:
=vlookup("Student 3",{D2:D5,A2:C5},2,FALSE)
The formula returns the “Test 1” mark of “Student 3”.
In the Sheets Vlookup formula, the index column number is 2. As per the actual data, it’s column 1. However, I’ve rearranged the columns. Now, column 4 that contains the student names is column 1, and the “Test 1” mark column is column 2.
Case Sensitivity – Vlookup Advanced Tips #5
To perform a case-sensitive Vlookup in Google Sheets, you can use a combo formula that involves the Vlookup and some other functions.
The Query function in Google Sheets is case-sensitive, so you can make use of its flexibility here.
Formula Example:
Let’s consider the following sample dataset. Pay attention to cells A4 and A5 in this dataset.
The values are the same, but in terms of case sensitivity, they are different, right?
- Value in A4: “IL102B”
- Value in A5: “IL102b”
Here, I want to return the Qty. (column 5) of the item “IL102b”. Will this Vlookup formula return the correct result?
=vlookup("IL102b",A2:G5,5,FALSE)
Nope! Vlookup is case-insensitive by default. So, it would return the Qty. of the item “IL102B” since it’s the first occurrence in column A.
Solution:
As mentioned before, the Query function is case-sensitive. So, first filter the data range A2:G5 as follows:
=query(A2:G5,"Select * where A='IL102b'")
This formula will return the row that contains “IL102b” in Column A. You can then use this Query as the range in Vlookup as shown below:
=vlookup("IL102b",query(A2:G5,"Select * where A='IL102b'"),5,FALSE)
There are a few more workarounds. To get more details, read this article: Case Sensitive Vlookup [Solved].
Named Ranges – Vlookup Advanced Tips #6
Can I use named ranges in Vlookup in Google Sheets?
Yes! You can use a named range in Vlookup. Here’s an example:
To name a range, first select the range you want to name. For example, let’s name the range A2:D5 as “sales”. Go to the menu, click on Data, and select “Named range” to name the range.
The below Vlookup formulas return the same outputs.
Vlookup Formula without the Named Range:
=vlookup("Student 3",A2:D5,4,false)
Vlookup Formula with the Named Range:
=vlookup("Student 3",sales,4,false)
Combine Search Keys and More Conditions – Vlookup Advanced Tips #7
In the sample data below, let’s say you want to find the “Cement” price from the Vendor “Z”. You can use the search key as follows in Vlookup: “CementZ”
Also, in the formula range, combine the columns that contain the item “Cement” and the Vendor Name “Z”.
A2:A7&B2:B7
Formula:
=ArrayFormula(vlookup("CementZ",{A2:A7&B2:B7,C2:C7},2,FALSE))
When you use Curly Brackets, wrap the Vlookup formula with the ArrayFormula. In short, you can use this type of Vlookup to accommodate multiple search keys (a.k.a criteria).
For more details, check out this article: How to Use VLOOKUP with Multiple Criteria in Google Sheets [Solved].
Image Vlookup – Vlookup Advanced Tips #8
No doubt, Image Vlookup is also possible in Google Sheets.
Let’s say you want to search for the item “Chicken Noodles” in Column A and return the picture from Column E (the fifth column).
=VLOOKUP("Chicken Noodles",A2:E3,5,FALSE)
There are no changes in the formula. If you want to know how to insert an image and fit it into a cell, the following resources can guide you:
- How to Use Image Function and Vlookup Images in Google Sheets
- Insert Images in Cells Without Formula in Google Sheets
Wildcard Characters – Vlookup Advanced Tips #9
How do you use Wildcard Characters in Vlookup in Google Sheets?
Let’s start with a basic example. Suppose you want to search for one product code “Y-10005” in column A and return a value from Column B. This Vlookup formula would probably return #N/A.
=vlookup("Y",A2:B6,2,FALSE)
To use the Asterisk wildcard with the search_key “Y”, modify the formula as follows:
=vlookup("Y*",A2:B6,2,FALSE)
If the search key is in another cell (e.g., cell D1), use the formula as mentioned below to include the asterisk wildcard character in Vlookup.
=vlookup(D1&"*",A2:B6,2,FALSE)
There are more wildcard characters that you can use in the Google Sheets Vlookup function.
Conclusion
You can refer to the guide below to learn more about the available wildcards in Google Sheets.
Must Read: How to Use Wildcard Characters in Google Sheets Functions
If you search on this site, you can find more Vlookup formula examples for your real-life use.
That’s all about the use of the Vlookup formula in Google Sheets. Enjoy!
Remember, if you want to become a Google Sheets expert and discover more useful tips and tricks, visit Crawlan.com.
