In this tutorial, I’ve included all the essential tips and tricks to master the VLOOKUP function in Google Sheets.

You’ll find 10 different variations of the VLOOKUP formula in Google Sheets, explained with practical examples.

Learn how to use the basic VLOOKUP function with a simple, beginner-friendly example. I hope even a newbie can follow along effortlessly.

After mastering the basics, explore the advanced tips, tricks, and formula variations for this popular function.

Function Syntax and Arguments

Syntax

VLOOKUP(search_key, range, index, [is_sorted])

Arguments Explained

• search_key: The value (text, number, date, etc.) to search for.
  • The search_key must exist in the first column of the range.
• range: The range or array of data to search within.
  • Only the first column of this range is searched for the specified search_key.
  • For searching other columns, a workaround is included in this tutorial.
• index: The column number from which to return a value. The first column of the range is column 1.
• is_sorted: (Optional) A Boolean argument that determines whether the search column is sorted.
  • FALSE (recommended): Searches for an exact match. If no match is found, #N/A is returned. Use the IFNA function to handle this error effectively.
  • TRUE (default): Searches for the closest match (≤ search_key). Use this only when the first column of the range is sorted in ascending order.

A to Z Guide to VLOOKUP in Google Sheets

I’ve done my best to include all possible variations of the VLOOKUP formula in this tutorial. Let’s dive in!

Basic Example Formula

Are you new to Google Sheets? Start here!

VLOOKUP is one of the most essential functions to learn if you want to become proficient in using spreadsheets.

Purpose of VLOOKUP in Google Sheets

This function searches vertically in the first column of a table or range for a search_key and returns a value from the column specified by index.

Let me break it down:

Suppose you want to find the “Test 2” score of “Student 3”.

• Use “Student 3” as the search_key.
• It’s in the first column.
• “Test 2” corresponds to the third column (index = 3).

Using VLOOKUP, you can extract the score (e.g., 86).

Formula Example:

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

This formula returns 86.

Key Tip:

Always specify the fourth argument (is_sorted) as FALSE unless you’re certain the range is sorted. This ensures an exact match.

Search Keys from a Range – VLOOKUP Advanced Tips #1

Google Sheets VLOOKUP can handle multiple search keys. For instance, if you want to look up the prices of “Apple” and “Mango” in the dataset below, how can you do that?

Simply reference a range of search keys and wrap the formula with the ARRAYFORMULA function as shown below.

=ARRAYFORMULA(VLOOKUP(D2:D3, A2:B5, 2, FALSE))

If you prefer to directly specify multiple search keys within the formula, enclose them in curly brackets ({}).

=ARRAYFORMULA(VLOOKUP({"Mango"; "Apple"}, A2:B5, 2, FALSE))

Similar: Retrieve Multiple Values Using VLOOKUP in Google Sheets

Return an Entire Row – VLOOKUP Advanced Tips #2

Contrary to what many might think, you can use the VLOOKUP function in Google Sheets to search the first column with a search key and return the entire row of the matching result. How?

Example VLOOKUP Formula to Return an Entire Row:

Simply list all the column numbers separated by commas, enclose them in curly brackets ({}), and wrap the entire formula with ARRAYFORMULA.

=ARRAYFORMULA(VLOOKUP("Student 2", A2:D5, {1, 2, 3, 4}, FALSE))

Make the Formula Flexible

The above formula works, but it can break if you insert or delete columns between columns A and D. To make it flexible, you can replace {1, 2, 3, 4} with the COLUMN function:

=ARRAYFORMULA(VLOOKUP("Student 2", A2:D5, COLUMN(A2:D5), FALSE))

Here’s how it works:

The COLUMN(A2:D5) formula dynamically returns the column numbers 1, 2, 3, 4 (corresponding to columns A, B, C, D), ensuring that your formula adapts if you modify the range structure.

This approach eliminates the risk of errors due to structural changes in your data.

Note: If your range starts from a column other than A (e.g., C2:E10), you should use COLUMN(C2:E10)-2. Here, 2 represents the number of columns before column C.

Variable in Column Index – VLOOKUP Advanced Tips #3

You can make the column index in VLOOKUP automatically adjust when columns are added or removed. For example, if you want to return a value from column 4, deleting a column would shift this column to 3, which could break your formula.

How can you ensure the VLOOKUP index column updates automatically with the range? Let’s see how to create a “sticky” column index in Google Sheets.

Solution: Use the COLUMNS Function

Instead of using a fixed column number, use the COLUMNS function in your formula. Unlike the COLUMN function, which returns the position of a single column, COLUMNS counts the number of columns in a range, ensuring flexibility.

For example:

=VLOOKUP("Student 2", A2:D5, COLUMNS(A2:D2), FALSE)

• The range A2:D2 contains four columns, so COLUMNS(A2:D2) returns 4.

If you want to return the value from the second-to-last column, you can adjust the formula like this:

=VLOOKUP("Student 2", A2:D5, COLUMNS(A2:D2)-1, FALSE)

This dynamic setup ensures the index adjusts automatically even if the structure of your data changes.

Leftward VLOOKUP – VLOOKUP Advanced Tips #4

In Google Sheets, the search key in VLOOKUP must always be in the first column of the range. That’s the rule, and unfortunately, you can’t change it. But what if your search key is in the fourth column? Don’t worry—there’s a simple workaround.

Here’s how you can use any column as the search key in VLOOKUP without physically rearranging your data.

Steps to Follow

You can rearrange the columns dynamically within the formula using curly brackets.

Assume your original range is A2:D4, and the search key (“Student 3”) is in the fourth column. To make it work, you can bring the fourth column to the first position like this:

{D2:D5, A2:C5}

This rearranges the columns temporarily for the VLOOKUP to work.

Now, see the formula:

=VLOOKUP("Student 3", {D2:D5, A2:C5}, 2, FALSE)

Explanation

• The formula searches for “Student 3” in the rearranged column (now the first column, D).
• It returns the value from the second column of the rearranged range, which corresponds to “Test 1”.

Key Details

• In this formula, the index column number is 2, but in the original data, it’s column 1 of the actual range.
• By rearranging the columns virtually, column 4 (student names) becomes column 1, and the “Test 1” column becomes column 2.

For more details, check out this guide: Reverse VLOOKUP Examples in Google Sheets [Formula Options].

Case Sensitivity – VLOOKUP Advanced Tips #5

To perform a case-sensitive VLOOKUP in Google Sheets, you’ll need to use a combination of VLOOKUP and other functions.

The QUERY function in Google Sheets is case-sensitive, so you can leverage this function to create a case-sensitive VLOOKUP.

Formula Example

Let’s first look at a sample dataset. Consider the following data in A4 and A5:

• A4: “IL102B”
• A5: “IL102b”

Although the values look similar, they are considered different by Google Sheets due to case sensitivity.

Now, suppose you want to return the Qty (column 5) for the item “IL102b”. Does this formula work?

=VLOOKUP("IL102b", A2:G5, 5, FALSE)

The answer is No! By default, VLOOKUP is case-insensitive, so it would return the quantity for “IL102B” since it’s the first occurrence in column A.

Solution

As mentioned earlier, QUERY is case-sensitive. So, to fix this, you can first filter the range using QUERY:

=QUERY(A2:G5, "SELECT * WHERE A = 'IL102b'")

This will return the row containing “IL102b” in column A. You can then use this filtered range as the lookup table in VLOOKUP:

=VLOOKUP("IL102b", QUERY(A2:G5, "SELECT * WHERE A = 'IL102b'"), 5, FALSE)

This formula will correctly return the Qty for “IL102b”.

For more detailed workarounds, read this guide: Case-Sensitive VLOOKUP [Solved].

Named Ranges – VLOOKUP Advanced Tips #6

Can you use named ranges in VLOOKUP in Google Sheets?

Yes! You can definitely use named ranges in VLOOKUP. Here’s how:

To name a range, start by selecting the range you want to name. For example, let’s say the range is A2:D5. Then, go to the menu, click on Data, and select Named range. You can then give this range a name—let’s call it “sales”.

Now, you can use this named range in your VLOOKUP formula. The formulas below will return the same result:

VLOOKUP Formula without Named Range:

=VLOOKUP("Student 3", A2:D5, 4, FALSE)

VLOOKUP Formula with Named Range:

=VLOOKUP("Student 3", sales, 4, FALSE)

Both formulas work the same way, but using named ranges can make your formulas cleaner and easier to understand.

You might want to check out this guide for a detailed tutorial on this specific topic: The Use of Named Ranges in VLOOKUP in Google Sheets.

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

In the example below, I want to find the price of “Cement” from Vendor “Z”.

You can combine the search keys in VLOOKUP like this:

Search Key: "CementZ"

To do this, combine the columns containing the item “Cement” and the Vendor “Z” in your formula range:

Formula Range: A2:A7 & B2:B7

Here’s the formula:

=ARRAYFORMULA(VLOOKUP("CementZ", {A2:A7 & B2:B7, C2:C7}, 2, FALSE))

When combining column ranges, make sure to wrap the VLOOKUP formula with ARRAYFORMULA. This approach allows you to use multiple search keys (or criteria) in a single VLOOKUP.

More Details: How to Use VLOOKUP with Multiple Criteria in Google Sheets

Image VLOOKUP – VLOOKUP Advanced Tips #8

No doubt, Image VLOOKUP is possible in Google Sheets.

For example, if I want to search for the item “Chicken Noodles” in Column A and return the image in Column E (the fifth column), I can use the following formula:

=VLOOKUP("Chicken Noodles", A2:E3, 5, FALSE)

The formula will return the image associated with “Chicken Noodles” from Column E.

If you want to know how to insert images and fit them into a cell, here are a couple of helpful links:

• Insert Images in Cells Without Formula in Google Sheets
• How to Use the IMAGE Function in Google Sheets

Wildcard Characters – VLOOKUP Advanced Tips #9

How to use Wildcard Characters in VLOOKUP in Google Sheets?

Here’s a basic example. Suppose I want to search for a product code starting with “Y”, like “Y-10005”, in Column A and return a value from Column B.

This VLOOKUP formula would likely return #N/A:

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

To handle this, you can use the Asterisk wildcard 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 following formula to include the asterisk wildcard character in VLOOKUP:

=VLOOKUP(D1 & "*", A2:B6, 2, FALSE)

There are other wildcard characters, such as the question mark and tilde, that you can use in the Google Sheets VLOOKUP function. The question mark represents a single character, while the tilde in front of a wildcard makes the formula treat it as a regular character.

Related: Wildcards in Vlookup Search Range in Google Sheets

Table References – VLOOKUP Advanced Tips #10

If you’re working with structured tables, you can use them in the range part of VLOOKUP.

Assume you have a table named “Table1.”

=VLOOKUP("Apple", Table1, 2, FALSE)

This formula will search for “Apple” in the first column of Table1 and return the result from the second column.

Conclusion

If you search this site, you’ll find more VLOOKUP formula examples for real-life use.

That’s all about using the VLOOKUP formula in Google Sheets. Enjoy!