Buy Now

How-to Guide: Master the VLOOKUP Google Sheets Function

Jun 01, 2022

The VLOOKUP function in Google Sheets can be utilized to search values in columns, and if a value has gotten found, return the value from that row via a specified column number.

Sounds complicated, right?

The VLOOKUP Google Sheets function is a tricky one for even the most knowledgeable Google Sheets users.

So, let's consider it in a different way.

Imagine you have gone out for drinks with your friends at an expensive bar. You are eying up the menu to decide what drink you want. You want something that sounds nice, but you are operating on a budget.

You have a look through the menu and discover a drink that you like the sound of, and then scroll across the menu to the right to work out how much it costs.

That is the real-life equivalent of how VLOOKUP in Google Sheets works!

VLOOKUP in Google Sheets goes down a list of content, identifies specific data within that column, and returns a corresponding value to that same row.

As you can imagine, there is a lot to learn about VLOOKUP in Google Sheets. Don't worry; I've got you covered.

VLOOKUP Syntax Google Sheets

Here is how the VLOOKUP formula looks in Google Sheets:

VLOOKUP(search key, range, index, [is sorted]).

Let's look at some of the main aspects that make up VLOOKUP in Google Sheets in more detail:

  • Is sorted: Set to TRUE by default. Here you can specify if you want to find an approximate match or an exact match. If you want to find the exact match, you should change this to FALSE or keep it as TRUE for an approximate match. If you choose the TRUE value, your list must be sorted into ascending order, or it will come out as an error.

  • Range: This term defines the range that will be implemented in the Google Sheets VLOOKUP function.

  • Index: This aspect refers to the column number where you wish to collect the result. This value must be in between one and the total amount of columns, or you will receive a #VALUE! Error.

  • Search key: This term is the item or value you are seeking out. In our example from the introduction, it would be a beer or a glass of coke, for example.

VLOOKUP Function Examples

VLOOKUP in Google Sheets: Using Wildcard for Partial Matches

Let's dive into our first VLOOKUP Google Sheets example.

A VLOOKUP function can also get used to searching for partial matches via wildcard characters. There are two compatible wildcard characters that you can use:

1) A question mark: Can be used to replace a single character.

2) An asterisk: Can be used to replace a sequence.

VLOOKUP in Google Sheets: Comparing Data Lists 

You can use the VLOOKUP function to compare two separate lists in Google Sheets.

For example, if you have two separate lists of names and you want to identify which names are missing from your first list, you can do so by following these steps:

1) Input "=VLOOKUP(" within a single cell. This formula won't work if you input it into multiple cells.

2) Use the very first cell from your original list as the "search key."

3) Use your second list for the range.

4) For the "is ascending" value, select "FALSE."

5) Hit the enter key, and click and drag your formula across to your desired cells.

VLOOKUP Function Hints and Tips

The VLOOKUP function can only look to the right. Using a VLOOKUP formula is not the right option for searching left. For this data range, you should use the MATCH function in Google Sheets.

A VLOOKUP formula is not case-sensitive, so you do not need to consider this when you set up your search key.

Troubleshooting VLOOKUP

Unfortunately, implementing a VLOOKUP formula in Google Sheets can cause many issues, either with some or all the data.

Here are a few easy ways to resolve any issues you might have with your Google sheets VLOOKUP.

If "Is Sorted" is set as true or omitted, ensure the first column of your formula's range is in ascending order.

If your Google Sheets VLOOKUP formula is showing you incorrect results, set "Is Sorted" to FALSE.

VLOOKUP Google Sheets: Key Summary and Takeaways

There you have it! Everything you need to know to start using the search key within the VLOOKUP function in Google Sheets.

If used correctly, the Google Sheets VLOOKUP function is an excellent way to find information in a Google Sheet. And it doesn't have to even be on the same sheet; VLOOKUP returns can be done across multiple sheets.

How to Do a VLOOKUP in Google Sheets FAQ

What is the VLOOKUP function in Google Sheets?

The term VLOOKUP is short for vertical lookup and allows you to crawl through columns based on their value.

Why use VLOOKUP on Google Sheets Files?

A Google Sheets VLOOKUP formula lets you implement a range of beneficial searches across multiple columns, including:

1) Associating lists

2) Discovering incorrect values

3) Identifying missing values across different lists that should be the same

4) Identifying partial matches or exact matches to search queries

Why is the VLOOKUP function not working in Google Sheets?

Here are the most common reasons why a VLOOKUP formula does not work in Google Sheets:

1) Attempting to search the furthest left columns via a right column. To do this, you should use the MATCH function.

2) Not creating a sorted column for the range before using a TRUE value for the "is ascending" aspect of the formula.

3) You are attempting to search case-sensitive queries, but VLOOKUP is not case-sensitive.

Is VLOOKUP Easy to Learn?

Executing a basic VLOOKup formula is not as tricky as you might think, and there are certainly more difficult things to master in Google Sheets than a VLOOKUP formula. Once you get to grips with the basics and study some VLOOKUP formula examples, you will be able to quickly advance your skillset.

How Do You Do a VLOOKUP Without Exact Match?

Ensure your range column has gotten sorted in ascending order. You can do this by clicking "Data" then "Sort Sheet."

Input the normal formula you would use.

Ensure the "is sorted" is set at TRUE.

Why Is My VLOOKUP Not Working Until I Click in Cell?

A VLOOKUP formula does not automatically update as it would impact many other formulas within more complex Google Sheets. Instead, you must manually update each VLOOKUP in Google Sheets.


Related Articles

How to Search in Google Sheets: 3 Quick and Easy Options

Google Sheets Dark Mode: Everything You Need to Know

Pivot Table Google Sheets Walkthrough

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.