In this tutorial, I have included all the necessary tips and tricks to master the function Vlookup in Google Sheets.

There are 10 different variations (1 basic and 9 advanced) of Google Sheets Vlookup included.

Learn Google Sheets Vlookup’s basic use with the help of my simple examples. I hope even a newbie can follow it.

After that go through different tips and tricks and formula variations related to the popular function Vlookup.

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 that 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.
  • is_sorted set to FALSE – Exact match. If multiple matches, the value from the first found row is returned. If no match, Sheets Vlookup will return #N/A error which you can make blank by simply wrapping Vlookup with IFNA function.
  • is_sorted set to TRUE – Nearest match. It means < or = to the search_key. If all values in the search column, the first column in the range, are > the search key, #N/A is returned. Here also use IFNA to return a blank.

Are you switched from Excel to Sheets?

Then don’t forget to read 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

I have tried my level best to include all the possible Vlookup formula variations in this tutorial. Let’s begin.

Basic Example Formula

Are you a newbie? Then this is the perfect spot to begin!

Needless to say Vlookup in Google Sheets is one of the first few functions that 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. Didn’t get? I’ll explain.

I want to find the “Test 2” mark of “Student 3”. Here I can use “Student 3” as the Vlookup search_key. It’s in the first column. The index of “Test 2” is 3.

Using Vlookup we can extract the mark 86. How? Use “Student 3” as the search key in Vlookup in Google Sheets and column number (index) as 3.

Basic Vlookup Formula Example:

=vlookup("Student 3",A2:C5,3,FALSE)

This formula can return the mark 86.

Don’t forget to specify the last argument which is FALSE. It indicates the range is not sorted and an exact match.

If your range is sorted, you can use TRUE or skip the last argument. But the best option is to always use FALSE. 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 return its “Price” from Column 2.

Search Keys from a Range – Vlookup Advanced Tips # 1

Sheets Vlookup takes multiple search keys. For example, I want to look up the prices of “Apple” and “Mango” in the below example. How to do that?

Use search key range reference and then just wrap the formula with the function ArrayFormula as below.

If you want to specify multiple search keys within Sheets Vlookup formula, enclose the multiple search keys within the Curly Brackets.

=ArrayFormula(vlookup({"Mango";"Apple"},A2:B5,2,FALSE))

Similar: How to Use Vlookup to Return An Array Result in Google Sheets

Return an Entire Row – Vlookup Advanced Tips # 2

Unlike many of you think, 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:

Just enter all the column numbers that separated by the comma and enclose it with the Curly Brackets/Curly Braces. Finally, wrap the entire formula with the ArrayFormula.

You can make this Vlookup formula more flexible. The above 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 above formula is replaced by theCOLUMN(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).

Similar Topic: Multiple Values Using Vlookup is Possible [How to]

Variable in Column Index – Vlookup Advanced Tips # 3

You can move the column Index along with the column. For example, I want to return the value from column 4. But if I insert a new column, this column number may become 5.

How to make the Vlookup index column number to 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. That’s the standard and you can’t change that. But there is a workaround. My Vlookup search key is in the fourth column. What can I do?

Here is the solution to use any column in Vlookup in Google Sheets as the search key column.

Steps to Follow

Rearrange the column position using Curly Brackets as below.

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. Then only the Vlookup work.

You can rearrange the column positions within the Vlookup in Google Sheets itself as below without physically altering the column positions.

{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”.

Here in the Sheets Vlookup formula, the index column number is 2. As per actual data, it’s column 1. But 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 do a case-sensitive Vlookup in Google Sheets, you should depend on a combo formula that involving the Vlookup and some other functions.

The function Query in Google Sheets is Case Sensitive. You can make use of this function’s flexibility here.

Formula Example

This time first I am sharing you with the sample dataset. See the cell A4 and A5 in this dataset.

The values are the same. But in the sense of case sensitivity, both are different values, right?

The value in A4: “IL102B”

The value in A5: “IL102b”

Here I want to return the Qty. (column 5) of the item “IL102b”. Does 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 I have told you, the function Query is case sensitive. So first we can filter the data range A2: G5 as below.

=query(A2:G5,"Select * where A='IL102b'")

This formula would return the row that contains “IL102b” in Column A. So you can use this Query as the range in Vloookup as below.

=vlookup("IL102b",query(A2:G5,"Select * where A='IL102b'"),5,FALSE)

There few more workarounds. Read that in detail here – Case Sensitive Vlookup [Solved]

Named Ranges – Vlookup Advanced Tips # 6

Can I use named ranges in Vlookup in Google Sheets?

Yes! You can use named range in Vlookup. Here is one example.

To name a range, first select the range to name. Here the range is A2:D5. Then from the menu > Data, you can select “Named range” and name the range. Here I am naming the range A2:D5 as “sales”

The below Vlookup formulas return the same outputs.

Vlookup Formula that without the Named Range:

=vlookup("Student 3",A2:D5,4,false)

Vlookup Formula that with the Named Range:

=vlookup("Student 3",sales,4,false)

Combine Search Keys and More Conditions – Vlookup Advanced Tips # 7

In the below sample data I want to find the “Cement” price of the Vendor “Z”. You can use the search key as below in Vlookup.

"CementZ"

Also, in the formula range, combine the columns that contain the item “Cement” and 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 concise you can use this type of Vlookup to accommodate multiple search keys aka criteria.

More Details: 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 Doc Sheets.

I want to search down the item “Chicken Noodles” in Column A and return the picture which is in Column E, i.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 image and fit it into a cell the below links can guide you.

1. How to Use Image Function and Vlookup Images in Google Sheets.
2. Insert Images in Cells Without Formula in Google Sheets.

Wildcard Characters – Vlookup Advanced Tips # 9

How to use Wildcard Characters in Vlookup in Google Sheets?

I am just including a basic example. Suppose I want to search one product code “Y-10005” in column A and return a value from Column B.

This Sheets Vlookup formula would probably return #N/A.

=vlookup("Y",A2:B6,2,FALSE)

Use the Asterisk wild card with the search_key “Y”.

=vlookup("Y*",A2:B6,2,FALSE)

If the Search Key is in another cell, for example in cell D1, use the formula as 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 Google Sheets Vlookup function.

Conclusion

You can follow the below guide to get to know more about the available wildcards in Google Sheets.

Must Read: How to Use Wildcard Characters in Google Sheets Functions.

If you do a search on this site, you can find more Vlookup formula examples for your reallife use.

That’s all about the use of Vlookup formula in Google Sheets. Enjoy!