Jul
5

Google Sheets VLOOKUP with examples

The tutorial explains the syntax of the Google Sheets VLOOKUP function and shows how to use Vlookup formulas for solving real-life tasks.

When working with interrelated data, one of the most common challenges is finding information across multiple sheets. You often perform such tasks in everyday life, for example when scanning a flight schedule board for your flight number to get the departing time and status. Google Sheets VLOOKUP works in a similar way - looks up and retrieves matching data from another table on the same sheet or from a different sheet.

A widespread opinion is that VLOOKUP is one of the most difficult and obscure functions. But that's not true! In fact, it's easy to do VLOOKUP in Google Sheets, and in a moment you will make sure of it.

Google Sheets VLOOKUP - syntax and usage

The VLOOKUP function in Google Sheets is designed to perform a vertical lookup - search for a key value (unique identifier) down the first column in a specified range and return a value in the same row from another column.

The syntax for the Google Sheets VLOOKUP function is as follows:

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

The first 3 arguments are required, the last one is optional:

Search_key - is the value to search for (lookup value or unique identifier). For example, you can search for the word "apple", number 10, or the value in cell A2.

Range - two or more columns of data for the search. The Google Sheets VLOOKUP function always searches in the first column of range.

Index - the column number in range from which a matching value (value in the same row as search_key) should be returned.

The first column in range has index 1. If index is less than 1, a Vlookup formula returns the #VALUE! error. If it's greater than the number of columns in range, VLOOKUP returns the #REF! error.

Is_sorted - indicates whether your Vlookup formula should return the nearest match (TRUE) or exact match (FALSE).

  • If is_sorted is set to FALSE, the Vlookup formula searches with exact match.

    If the lookup column (the first column of range) contains 2 or more values exactly equal to search_key, the 1st value found is returned. If an exact match is not found, the #N/A error is returned. In most cases, it's recommended to Vlookup with exact match.

  • If is_sorted is TRUE or omitted (default), a Vlookup formula returns an approximate match. The lookup column must be sorted in ascending order, i.e. from A to Z or from smallest to largest.

    Vlookup with approximate match works this way: the formula searches for the exact match first. If the exact match is not found, it searches for the closest match that is less than or equal to search_key. If all values in the lookup column are greater than the search key, the #N/A error is returned.

At first sight, the syntax may seem a bit complicated, but the below Google Sheet Vlookup formula example will make things easier to understand.

Supposing you have two tables: main table and lookup table like shown in the screenshot below. The tables have a common column (Order ID) that is a unique identifier. You aim to pull the status of each order from the lookup table to the main table.
Source data: main table and Lookup table

Now, how do you use Google Sheets Vlookup to accomplish the task? To begin with, let's define the arguments for our Vlookup formula:

Search_key - Order ID (A3), the value to be searched for in the first column of the Lookup table.

Range - the Lookup table ($F$3:$G$8). Please pay attention that we lock the range by using absolute cell references since we plan to copy the formula to multiple cells.

Index - 2 because the Status column from which we want to return a match is the 2nd column in range.

Is_sorted - FALSE because our search column (F) is not sorted.

Putting all the arguments together, we get this formula:

=VLOOKUP(A3,$F$3:$G$8,2,false)

Enter it in the first cell (D3) of the main table, copy down the column, and you will get a result similar to this:
Vlookup in Google Sheets

Is the Vlookup formula still difficult for you to comprehend? Then look at it this way:

Google Sheets Vlookup formula

5 facts you should know about Google Sheets VLOOKUP

As you already understood, the Google Sheets VLOOKUP function is a thing with nuances. Remembering these five simple facts will keep you out of trouble and help you avoid most common Vlookup errors.

  1. Google Sheets VLOOKUP cannot look at its left, it always searches in the first (leftmost) column of the range. To do a left Vlookup, use Google Sheets Index Match formula.
  2. Vlookup in Google Sheets is case-insensitive, meaning it does not distinguish lowercase and uppercase characters. For case-sensitive lookup, use this formula.
  3. If VLOOKUP returns incorrect results, set the is_sorted argument to FALSE to return exact matches.
  4. When is_sorted set to TRUE or omitted (Vlookup with the closest match), remember to sort the first column of range in ascending order.
  5. Google Sheets VLOOKUP can search with partial match based on the wildcard characters: the question mark (?) and asterisk (*). Please see this Vlookup formula example for more details.

How to use VLOOKUP in Google Sheets - formula examples

Now that you have a basic idea of how Google Sheets Vlookup works, it's time to try your hand in making a few formulas on your own. To make the below Vlookup examples easier to follow, you can open the sample Vlookup Google sheet.

How to Vlookup from a different sheet

In real-life spreadsheets, the main table and Lookup table often reside on different sheets. To refer your Vlookup formula to another sheet within the same spreadsheet, put the worksheet name followed by an exclamation mark (!) before the range reference. For example:

=VLOOKUP(A2,Sheet4!$A$2:$B$7,2,false)

The formula will search for the value in A2 in the range A2:A7 on Sheet4, and return a matching value from column B (2nd column in range).

If the sheet name includes spaces or non-alphabetical characters, be sure to enclose it in single quotation marks. For example:

=VLOOKUP(A2,'Lookup table'!$A$2:$B$7,2,false)

Vlookup from a different sheet

Tip. Instead of typing a reference to another sheet manually, you can have Google Sheets insert it for you automatically. For this, start typing your Vlookup formula and when it comes to the range argument, switch to the lookup sheet and select the range using a mouse. This will add a range reference to the formula, and you will only have to change a relative reference (default) to an absolute reference. To do this, either type the $ sign before the column letter and row number, or select the reference and press F4 to toggle between different reference types.

Google Sheets Vlookup with wildcard characters

In situations when you do not know the entire lookup value (search_key), but you do know a part of it, you can do a lookup with the following wildcard characters:

  • Question mark (?) to match any single character, and
  • Asterisk (*) to match any sequence of characters.

Let's say you want to retrieve information about a specific order from the table below.  You cannot recall the order Id in full, but you remember that the first character is "A". So, you use an asterisk (*) to fill in the missing part, like this:

=VLOOKUP("a*",$A$2:$C$7,2,false)

Better yet, you can enter the known part of the search key in some cell and concatenate that cell with "*" to create a more versatile Vlookup formula:

To pull the item: =VLOOKUP($F$1&"*",$A$2:$C$7,2,false)

To pull the amount: =VLOOKUP($F$1&"*",$A$2:$C$7,3,false)
Google Sheets Vlookup with a wildcard character

Tip. If you need to search for an actual question mark or asterisk character, put a tilde (~) before the character, e.g. "~*".

Google Sheets Index Match formula for left Vlookup

One of the most significant limitations of the VLOOKUP function (both in Excel and Google Sheets) is that it cannot look at its left. That is, if the search column is not the first column in the lookup table, Google Sheets Vlookup will fail. In such situations, use a more powerful and more durable Index Match formula:

INDEX (return_range, MATCH(search_keylookup_range, 0))

For example, to look up the A3 value (search_key) in G3:G8 (lookup_range) and return a match from F3:F8 (return_range), use this formula:

=INDEX($F$3:$F$8, MATCH (A3, $G$3:$G$8, 0))

The following screenshot shows this Index Match formula in action:
Google Sheets Index Match formula for left Vlookup

Another advantage of the Index Match formula compared to Vlookup is that it is immune to structural changes you make in the sheets since it references the return column directly. In particular, inserting or deleting a column in the lookup table breaks a Vlookup formula because the "hard-coded" index number becomes invalid, while the Index Match formula remains safe and sound.

For more information about INDEX MATCH, please see Why INDEX & MATCH is a better alternative to VLOOKUP. Though the above tutorial targets Excel, INDEX MATCH in Google Sheets works exactly the same way, except for different names of the arguments.

Case-sensitive Vlookup in Google Sheets

In cases when the text case matters, use INDEX MATCH in combination with the TRUE and EXACT functions to make a case-sensitive Google Sheets Vlookup array formula:

ArrayFormula(INDEX(return_range, MATCH (TRUE,EXACT(lookup_range, search_key),0)))

Assuming the search key is in cell A3, the lookup range is G3:G8 and the return range is F3:F8, the formula goes as follows:

=ArrayFormula(INDEX($F$3:$F$8, MATCH (TRUE,EXACT($G$3:$G$8, A3),0)))

As shown in the screenshot below, the formula has no problem with distinguishing uppercase and lowercase characters such as A-1001 and a-1001:
Case-sensitive Vlookup in Google Sheets

Tip. Pressing Ctrl + Shift + Enter while editing a formula inserts the ARRAYFORMULA function at the beginning of the formula automatically.

Vlookup formulas are the most common, but not the only, way to look up in Google Sheets. The next and the final section of this tutorial demonstrates an alternative.

Merge Sheets - simpler way to do Vlookup in Google Sheets

If you are looking for a visual formula-free way to do Google spreadsheet Vlookup, consider using the Merge Sheets add-on. You can get it for free from the Google Sheets add-ons store.

Once the add-on is added to your Google Sheets, you can find it under the Add-ons tab:
Merge Sheets add-on

With the Merge Sheets add-on in place, you are ready to give it a field test. The source data is already familiar to you: we will be pulling information from the Status column based on the Order ID:
Source data to merge sheets based on the key column

  1. Select any cell with data within the Main sheet and click Add-ons > Marge Sheets > Start.

    In most cases, the add-on will pick up the entire table for you automatically. If it doesn't, either click the Auto select button or select the range in your main sheet manually, and then click Next:
    Select the range in the main sheet.

  2. Select the range in the Lookup sheet. The range does not necessarily have to be the same size as the range in the main sheet. In this example, the lookup table has 2 more rows than the main table.
    Select the range in the lookup sheet.
  3. Select one or more key columns (unique identifiers) to compare. Since we are comparing the sheets by Order ID, we select only this column:
    Select one or more key columns to compare.
  4. Under Lookup columns, select the column(s) in the Lookup sheet from which you want to retrieve data. Under Main columns, choose the corresponding columns in the Main sheet into which you want to copy the data.

    In this example, we are pulling information from the Status column on the Lookup sheet into the Status column on the Main sheet:
    Select the column(s) to be updated.

  5. Optionally, select one or more additional actions. Most often, you'd want to Add non-matching rows to the end of the main table, i.e. copy the rows that exist only in the lookup table to the end of the main table:
    Select one or more additional options.

Click Finish, allow the Merge Sheets add-on a moment for processing, and you are good to go!
The Merge Sheets results

That's how you can do Google Sheets lookup. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard