How to use VLOOKUP in Google Sheets

If the basic LOOKUP function left you feeling a little underwhelmed, I'm sure you'll appreciate the VLOOKUP. It’ll help you sort out those table sorting issues – pun intended!

Also, this is the only tutorial you need to check if you want to know everything about how to use VLOOKUP in google sheets.

Your VLOOKUP Step-by-Step guide

Copy of the spreadsheet

The VLOOKUP function works with every table; it doesn't matter if it is a Pivot table or a table array like the ARRAYFORMULA ones.

Like the LOOKUP function, VLOOKUP is used to fetch a value from a given column in a table, where you know the value of another column.

If you were wondering, that "V" before the "LOOKUP" name stands for Vertical. In fact, VLOOKUP stands for Vertical Lookup because it searches through columns, hence the vertical.

For example, suppose you have a table with the supplies from a provider, where you know the name of the product, and want to see the number of items available or the price. As long as they are on a table where each column has a defined type of information, VLOOKUP can be your way to go.

In this example, we have exactly that kind of table.

For the VLOOKUP function to work, we need to search for a term that exists in a given range or table. In this case, it would be A2:D21. This means that we can search for any term that exists in the first column of the range. In this particular example, the item names, and retrieves one particular value from one specific column, which is also known as the index, and is identified by a number.

VLOOKUP in Google Sheets is case-insensitive and works with both numbers and strings (also known as text). However, VLOOKUP cannot return multiple values; it will always return just one value. If you want to return multiple values, you need to concatenate two or more VLOOKUP functions.

The only pitfall of the VLOOKUP function is that for it to work best, your table needs unique values, as it will retrieve only the first match it finds.

VLOOKUP Syntax

As for the VLOOKUP syntax, it's quite straightforward:

=VLOOKUP(“Search_Term”, “LOOKUP_Table”, Index, is_sorted)

Let me explain the parameters:

• Search_Term: This is the string of text or number that you know and are searching for in the table. It can either be a dynamic value generated via formula or the value in a given cell. If your search term is a number, you don’t need to use quotation marks, but if you are looking for a string of text, you need them.

• LOOKUP_Table: This is the range that contains at least both your search term and the value you want to fetch. Normally, it's the entire range of the table.
• Index: The index is a number, more precisely the numeric value of the column containing the information that you are looking for. It's calculated by counting the number of columns in the 'LOOKUP_Table'. If our range was B2:E21, B would be 1, and E would be 4.
• Is_sorted: This is an optional value and tells Google Sheets (and any spreadsheet software) whether you're looking for an exact match in the column containing the search term. Although this is optional, if you omit it, VLOOKUP will assume that it is sorted and you don’t need to specify it. You can either write "TRUE" or "FALSE", but it works perfectly fine with 1 or 0, where one stands for "TRUE". If you want to use the approximate match, you should sort the table based on the content of the column with your search term.

In our example, one version of this formula could be:

=VLOOKUP(A21, A2:D21, 2, 0)

In this case, we are looking for:

• The value in A21 (“Fusion Reactor Coolant Cells”).
• In a range from A2 to D21 (the whole table).
• We are looking for the value in the second column (2) of the range (in this case the price).
• We need an exact match (0, but you can use FALSE) of the value in column A (the column containing the value that we are searching).

And we get the price indeed

VLOOKUP Examples

In this section, we'll explore examples. I've gathered various ways where the VLOOKUP function can be useful, but ultimately, the only limit is your imagination (and the function's constraints).

Before we delve into specific scenarios, here are a couple of general examples:

In the same table, the VLOOKUP searches for the content of A21, the last entry in the table, which happens to be “Fusion Reactor Coolant Cells”. We can see that its price is “$6,300”.

In the first example, VLOOKUP returns the correct price because of the “0” or “FALSE” parameter, which forces it to find an exact match. In the next two examples, where it is set to “1” or “TRUE” or not set at all, the returned value is “$4,900”, which certainly doesn’t correspond to our item.

If we sort the table by the values in column A, we can observe that the value we are looking for is now “Vibro-Magnetic Pulse Generators”, as it’s the content of A21 (our search key). Surprisingly, all three cases work as they all return “$4,200”, regardless of how we use the “is_sorted” parameter.

If you were wondering why Google documentation calls the last parameter “is_sorted,” even though it explains that you choose between “Approximate match” and “Exact match,” wonder no more. In fact, what VLOOKUP does with “Approximate match” has a lot to do with sorted tables.

The advantage, when comparing Vertical LOOKUP with normal LOOKUP, is that now we have an option to work with unsorted records. This is valuable information, and we will delve into this during the error handling section of this article.

Use VLOOKUP with drop down lists

In this example, we will see how to use VLOOKUP with dropdown menus.

First, let’s discuss the benefits of doing so, which are mainly related to reducing the possibility of human error and automation. Having a string in the formula may not always be the best practice when it comes to propagating the formula elsewhere, where the search key needs to be different. However, it’s mostly about avoiding human errors; the less we write, the less likely we are to make mistakes.

So, this second set of formulas works exactly with a dropdown menu. This menu is nothing but the list of items in the table, and the dropdown menu is in cell G13.

Essentially, there is nothing different from the previous example, except for the fact that now we can change our choice much more easily than before. Here, we are not required to touch the formula, as it refers to our dropdown menu cell (G13). Additionally, for a change, I used “TRUE” and “FALSE” instead of “1” and “0” to deal with the exact or approximate match.

VLOOKUP on another sheet

In Google Sheets, but I would dare to say in every spreadsheet software, it doesn’t really matter where the data is; if you want to use it, VLOOKUP is no exception to this rule, and it is perfectly possible to run VLOOKUP from another sheet. In fact, I would confirm that VLOOKUP is mostly going to be used on a different sheet, so that the table is on its own sheet.

Here, we have another sheet called “VLOOKUP Sheet 2” (because I lack imagination for a better name) where we have a made-up list of F1 parts, with made-up prices, inventory, and contacts.

In the old sheet, the example in yellow, is exactly about that.

We have the usual three sets of formulas, where the major difference consists of the name of the range:

=VLOOKUP(G22,'VLOOKUP Sheet 2'!A2:D21,2,FALSE)

You can notice that the formula now searches for a different dropdown menu (in G22) because we have different names, and the range is no longer a simple range of columns and rows. Instead, it has a string in quotation marks, and the usual range of columns and rows is separated by it with an exclamation mark:

‘VLOOKUP Sheet 2’!A2:D21

The good old (and hopefully familiar) A2:D21 doesn’t need further explanation; it is just the range of the table. But being the same range as the old table, how could Google Sheets understand that we are referring to a different sheet? That’s when the name of the sheet in quotation marks comes in handy. In fact, “VLOOKUP Sheet 2” is nothing but the name of the second sheet.

You can easily create it while typing the formula. If you start typing this:

=VLOOKUP(G22,

You will have the chance to physically go to the sheet containing the range that goes next in the formula and drag a selection of the table. It will automatically populate the formula for you.

Like in the previous examples, when the content of the table is not sorted and VLOOKUP either specifies the "is_sorted" parameter as "TRUE" or doesn’t specify it at all, the results are incorrect. We can see that the price of a tire, for instance, is "$25,000" in the last two examples, while in the first, the price is right at "$2,000" as it should be.

VLOOKUP with MATCH

Without delving too deeply into what the MATCH function does (for that, I have a dedicated article), it generates an index based on the term we search, either from a row or a column.

As shown in the image below, the MATCH formula looks for the value in F31 (Items) and returns its index (1).

If we change the value of the dropdown menu to "Contact," MATCH returns 4. This occurs because I set MATCH to search within the header of the table, and it returns the number of the column.

Now, in G31, we have our old dropdown menu with the list of items, and the VLOOKUP formula on top, with this syntax:

=VLOOKUP(G31, A2:D21, F27, 0)

This is the usual VLOOKUP, which we have seen already in the previous examples. We look for the value of G31 in the range of the table A2:D21. Instead of the index, as we used so far, we now refer to the cell with the MATCH function (F27), and we force the exact match with the 0 (or FALSE) at the end.

The result, in this example, is the email address of the supplier.

Now, we have both items to look for and the column with the result that we want, in two dropdown menus, respectively in G31 and F31.

If we change F31 and choose Price, the VLOOKUP formula will return the price because we refer to the number 3 (in cell F27).

Despite this astonishing result, which allows us to retrieve literally any value from the table, we can admit that this setup is a little too complex.

This is why we have the final example below, where we replace F27 (which pointed to the result of MATCH) with the MATCH formula instead. Here's how it looks:

=VLOOKUP(G31, A2:D21, MATCH(F31, A1:D1, 0), 0)

Without dissecting the VLOOKUP formula again, we see that the third parameter is the match formula:

MATCH(F31, A1:D1, 0)

All it does is generate a number between 1 and 4 based on the selection of the dropdown in F31. This demonstrates how you can use MATCH with VLOOKUP.

VLOOKUP to compare two columns

When comparing two columns with VLOOKUP, we typically use a combination of functions. However, we can demonstrate the principle of comparison using just a mathematical operator. In this case, we'll use the lesser-than "<" operator and have VLOOKUP return either TRUE or FALSE based on the threshold of items in inventory, which has its own sheet and table called "VLOOKUP Sheet 3".

We can observe that this is the Formula 1 table, but it includes two additional columns, "Low stock" and "Reorder".

Based on the sales velocity of each item, we need to reorder if the items in stock fall below the low stock column (thus the comparison).

The "Reorder" column contains the formula, and I've also included the syntax of each row in the "Formula" column afterward.

Here is the syntax for row 2:

=VLOOKUP(A2, A2:D21, 3, 0) < E2

Here, we check the inventory in column A using the usual syntax (non-sorted), then we create a statement indicating that the value returned by VLOOKUP is less than the value in column E. Google Sheets determines whether our statement is true or false. In this case, it's false because column E has a value of 5 and the inventory is also 5, meaning that the inventory value is not lower than the value in column E.

If we check row 4, the gearbox, we can see that it shows "TRUE" because the value in column E (10) is greater than the inventory (8).

This demonstrates how we can compare two columns using just VLOOKUP. However, we can improve this approach as the "TRUE" and "FALSE" outcomes may not be very conspicuous.

VLOOKUP with conditional formatting

How can we make VLOOKUP highlight a match? The solution is simple: through conditional formatting.

We can even convert the entire column F into a data validation tickbox to make it visually clearer. By doing this, we can easily understand that if the column is checked, we need to reorder. Additionally, we can apply conditional formatting to display the cell in red if it's true (indicating it's time to reorder) and green if it's not.

Combining VLOOKUP with HYPERLINK

To enhance our table's usability, we can create links to the email addresses of each provider, allowing us to contact them with a simple click to reorder items that we're running out of.

Since this article focuses on VLOOKUP, I won’t delve into details, but here’s the syntax:

=HYPERLINK("mailto:" & VLOOKUP(A2, A2:D21, 4, 0), "Email provider")

In this example, we use VLOOKUP to generate the email address of each provider. Although it may seem redundant, it serves the purpose of this demonstration.

The HYPERLINK function generates URLs, including a "mailto:" link, which opens your email client and fills in the recipient dynamically using VLOOKUP to search in column 4 (containing the email addresses of the provider).

The "&" symbol concatenates two values: "mailto:" and the email address. For instance, in row 2, it would generate "This email address is being protected from spambots. You need JavaScript enabled to view it.This email address is being protected from spambots. You need JavaScript enabled to view it.". The label "Email provider" is the text displayed in the cell.

With additional effort, we could extract the domain from the email address and label it as "Visit provider," but that's a topic for another tutorial.

Wildcards in VLOOKUP

VLOOKUP with wildcards is a fascinating function and, to my knowledge, it is not supported by all spreadsheet software, making it one of the advantages of Google Sheets.

If you're unfamiliar with the wildcard concept, it allows you to match your searched item based on partial search criteria. For instance, you can specify the first letter, last letter, or letters in between, and you're not limited to just one letter.

What VLOOKUP does is return the first element that matches your search criteria. You can denote the wildcard with the asterisk symbol "*", as shown in this example syntax:

=VLOOKUP("n*",A2:D21,2,FALSE)

Let's focus on the first parameter, "n*." In our first example, it returns "$3,800," corresponding to "Neutrino Oscillation Modulators." This is because VLOOKUP automatically searches for the value in the first column of the range (A2:D21), which contains the items.

The asterisk in "n" signifies "whatever content," while the "n" to its left indicates that the term we're looking for must begin with "n" (not case-sensitive).

If the asterisk is only at the end of the string, it implies that there is nothing before the "n," meaning the word must begin with the letter "n." The same applies if the asterisk is at the beginning of the string, but in this case, we can only use "*s" for a match because all the items end with "s."

You can get creative and use more than one asterisk. For example:

=VLOOKUP("*ve*",A2:D21,2,FALSE)

This would find a match in the second row of the table, searching for the first item that has "ve" anywhere within its name.

You can further experiment by placing one letter, then one asterisk, and another letter, like so:

=VLOOKUP("q*m*",A2:D21,2,FALSE)

This would return "$5,500," corresponding to the price of "Quantum Flux Capacitors" in row 3. However, note that there's a potential pitfall with wildcards:

We don’t have any item that begins with “q”, has a letter, and is immediately after “m”.

If we analyze the word “Quantum” we can see that between the “q” and the “m” there is certainly more than one character.

That’s because it doesn’t matter how many asterisks there are in your string, for Google Sheets, it would still represent “whatever there is in between”, disregarding anything else.

We can also notice that in this case, the asterisk after the “m” is disregarded as it’s the last letter of the word.

VLOOKUP Errors

So far, we've explored all the great features that Google Sheets VLOOKUP offers, but now let's take a look at its downside.

Errors can occur in various forms. For instance, if VLOOKUP isn't functioning and doesn't display the formula, it could mean you've forgotten the "=" sign.

But if VLOOKUP isn't working and doesn't show the formula, here are several potential reasons.

Before we dive into the errors, let's consider a scenario where VLOOKUP might return an incorrect value.

This issue is often linked to not specifying the "is_sorted" parameter, which defaults to true unless otherwise stated. In such cases, if your search column isn't sorted, you may end up with the wrong value. To rectify this, you can try explicitly setting the parameter to "FALSE" (or "0") at the end of your required parameters, as discussed earlier in this article.

Another common mistake is inadvertently adding trailing spaces in your table. Although similar to your search term, these spaces can cause discrepancies, especially for a computer, which treats them as distinct. Implementing a dropdown menu populated with table items can help alleviate such issues by eliminating room for such errors.

Now, let's delve into specific error scenarios.

To Wrap Up

You made it to the end! Congratulations!

In this article, we have explored numerous uses of VLOOKUP. I hope it has helped you determine whether it's suitable for your needs. In summary, if you're not dealing with a large amount of data, don't need to retrieve values from columns preceding the known value, and only need to return a single value, VLOOKUP is a great alternative to the normal LOOKUP function because it doesn't require data sorting in your table.