*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.

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.

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 2^{nd}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:

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

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.

- 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. - Vlookup in Google Sheets is
**case-insensitive**, meaning it does not distinguish lowercase and uppercase characters. For**case-sensitive lookup**, use this formula. - If VLOOKUP returns incorrect results, set the
*is_sorted*argument to FALSE to return exact matches. - When
*is_sorted*set to TRUE or omitted (Vlookup with the closest match), remember to sort the first column of*range*in ascending order. - 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.

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.

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 (2^{nd} 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)`

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)`

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_key*, *lookup_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:

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.

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:

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.

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:

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*:

- Select any cell with data within the
**Main sheet**and click*Add-ons*>*Merge 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
**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 one or more
**key columns**(unique identifiers) to compare. Since we are comparing the sheets by*Order ID*, we select only this column:

- 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:

- 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:

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

Multiple VLOOKUP Matches is another Google Sheets tool for advanced lookup. As its name suggests, the add-on can return all matches, not just the first one as the VLOOKUP function does. Moreover, it can evaluate **multiple conditions**, look up in **any** **direction**, and return all or the specified number of matches as **values **or** formulas**.

Remembering that a picture is worth a thousand words, let's see how the add-on works on real-life data. Supposing, some orders in our sample table contain several items, and you wish to retrieve all the items of a specific order. A Vlookup formula is unable to do this, while a more powerful QUERY function can. The problem is this function requires knowledge of the query language or at least SQL syntax. Have no desire to spend days studying this? Install the Multiple VLOOKUP Matches add-on and get a flawless formula in seconds!

In your Google Sheet, click *Add-ons* > *Multiple VLOOKUP Matches* > *Start*, and define the lookup criteria:

- Select the range with your data (A1:D9).
- Specify how many matches to return (all in our case).
- Choose which columns to return the data from (
*Item*,*Amount*and*Status*). - Set one or more conditions. We want to pull the information about the order number input in F2, so we configure just one condition: Order ID = F2.
- Select the top-left cell for the result.
- Click
*Preview result*to make sure you will get exactly what you are looking for. - If all is good, click either
**Insert formula**or**Paste result**.

For this example, we chose to return matches as formulas. So, you can now type any order number in F2, and the formula shown in the screenshot below will recalculate automatically:

To learn more about the add-on, visit the Multiple VLOOKUP Matches home page or get it now from the G Suite Marketplace.

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

Excel formulas
CSV
Excel functions
Print
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 45 Responses to "Google Sheets VLOOKUP with examples"

Svetlana

Your Great

Thanks countless

Thank you guys,

This post is the best!

Great post - thanks!

Now, can I do the vlookup to a different spreadsheet? And if so, how do I reference it?

Hi Katie,

To do vlookup from a different spreadsheet, embed the IMPORTRANGE function in the range argument, like this:

=VLOOKUP(A2, IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc", "Sheet1!A1:C10"), 3, false)

Hi Svetlana,

Thank you for sharing this tip, it could be the answer to one of the biggest problems I have and it should help me get around the Cell maximums that Google Sheets imposes on my spreadsheets.

I tried the IMPORTRANGE part and I get a message that says:

Error

VLOOKUP evaluates to an out of bounds range.

I am wondering, are there limits to the size of the lookup range if i use IMPORTRANGE within a VLOOKUP?

put the =importrange in a cell and respond to the dialog to connect the sheets. then you can use importrange in another command

Thank you for this post!! Super helpful!!

I have a google sheet for hiring part-time staff. The sheet has 2 tabs - one for hiring students and one for hiring not-students. The column headings in both tabs are the same, but do not appear in the same columns. So I have columns A, B, C equal to status, first name, last name in both tabs. But for hire date, hourly rate, etc. they might appear in Column E in one tab and Column R in the other. And what I want to do is create a master sheet with all the information for both tabs.

Is this possible?

Thanks -

Hi Ann,

Please don’t worry about your column order — to Ablebits’ ‘Combine sheets’ tool it’s not that big a deal. Just specify the ranges in ‘Step 1’ (to be on the safe side) and keep a tick next to ‘Consider table headers’ in ‘Step 2’ (that really matters).

I still don't get it

Hello, Min,

Could you please specify what remains unclear?

If you'd like, you can check the article about VLOOKUP in Excel, they work the same.

Hi

How to get the answer from sheet2 when look up value is in sheet1.

Oh, my God, thank you! I finally got it to work because of your explanation. My project is back on track and off to the races!

Thank you, this is great! I'm having trouble with copying the formula all the way down the sheet though - I have approx 1000 rows and need the lookup to go row-by-row to check the whole range. But when I copy the formula by dragging the crosshair down the sheet, the formula automatically changes the lookup range to start looking up in the matching row in the lookup sheet (so instead of searching A2:F1001, it will change to A3:F1002 then A4:F1003, and so on all the way down). How do I keep it so that the lookup range stays the same, but the search_key changes for each row number??

Hi Jenny,

Just lock the lookup range by using absolute cell references ($A$2:$F$1001) and it will stay the same when you copy the formula.

How to move the lookup range automaticaly to include new data after input in Google Sheet? Can the Table function applied in Google Sheet?

I have two worksheets and I want to type in the work order # on the second work sheet and it will autopopulate from the information on the first sheet under that work order number but I am having difficulty in getting the formula right - it has several please where I need this formula to look back at the first sheet for that row - I hope I am explaining myself so that you understand.

Dear Madam

I need to pull data from two different tables with two different cells. Is it possible to cull out data from two different excels by using Vlookup and Hlookup together

Kindly help

HI, i am using vlookup to match values from different sheets , it gives me an error , is it any mistake in my formula ? Thank you

=VLOOKUP(D2,IMPORTRANGE("0B1X38-hjZ0iONFZNX0d0RjU0RTU1OGVtRS1VOTlpMmlMM013","ContactPerson!A:C"),3,0)

Hi Elie,

As far as I can see, the link you use in the IMPORTRANGE is incorrect. Please refer to the Google help page for this function to see the examples of how it should look like:

https://support.google.com/docs/answer/3093340?hl=en

Hello Miss,

Thank you for all the information. Also, With Add-ons, is the first time I can match the Id code with different sheets, however I would like to sum all the Id code with different sheets in only one shell. How Can I do that?

Thank you in advance.

Hello Oscar,

Thank you for your interest in our products.

For us to be able to suggest you better, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.

Note.We keep support@4-bits.com for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm in this message thread.We'll look into your task and see if our software can help.

Hi wonder if anyone can help I’m new to Vlookup so sorry if I seem like a complete idiot lol

Basically I’ve set a table that’s about 300 rows and it contains data that’s the same with other 150 names now I use Vlookup to pull the data from on sheet to another to get the information I need great! But what if the same name I’m searching for has multiple rows that new to be pulled across how do I do this as Vlookup only pulls the first row it sees that matches

Hi Anthony,

The only way to do that in Google Sheets is by using the QUERY function, like this:

=QUERY(Sheet1!A1:C6,"SELECT A, B, C WHERE ((A = 'NAME'))", false)

Where

A1:C6can be your entire table andA, B, Care columns to return wheneverNAMEappears incolumn A.We haven't described QUERY in detail on our blog yet, so you can learn the function here.

may I know how to vlookup in different forms?

Alicia,

all Google forms return their results to spreadsheets.

To do a v-lookup from another spreadsheet, you need to add IMPORTRANGE to your formula. Svetlana has provided the example here:

https://www.ablebits.com/office-addins-blog/2017/07/05/vlookup-google-sheets-example/comment-page-1/#comment-321671

How can I do vlook up with the total num of OTs?

Hello Redd,

for us to be able to suggest you anything, please describe your task in detail. Thank you.

Excellent article on vlookup using google sheets, works like a charm. Thanks for the detailed explanation and examples

It says it cannot find my value in the VLOOKUP evaluation, even though I double-checked and the value is definitely in the left column. How can I fix this?

Hi Sydney,

We've explained some moments that may result in VLOOKUP errors in this blog post. Please have a look and let us know if none of the solutions works for you.

Hi, the post is awesome, but it took me hours to find out what can be the reason of none of the formulas working for me.

Since I use the hungarian version og Google sheet, I should use hungarian formulas, with semicolon as separating parameters insted of commas. (Google sheet specific formulas should used in english. For example: IMPORTRANGE)

Maybe it will help for others also who using the google sheet not english version.

All the best:

Laszlo

Hi László,

You're right, your spreadsheet locale dictates the delimiters that should be used in all your formulas. I've added this info to our article on possible VLOOKUP errors as the first thing to check. Thank you very much! :)

Hi want to use Vlookup and take data to a slide presentation from an excel sheet - can I do that ?

Hi Varsha,

Since Excel and Google Slides are completely different platforms, there's no way to connect them.

The only thing I can suggest is to convert your Excel file into Google Sheets. You can import your Excel file to Sheets via

File > Import > Upload > Select a file from your device.My Query is that - If want to write Yes / No in E Cell if Name in D cell is present in list from A Column. Can you please help with this

Please refer the sheet - https://docs.google.com/spreadsheets/d/1iaemtTWh1JoC-8wucvoTRwhmmXj6VqxB9LuHgrJ1jmw/edit?usp=sharing

Hello Jitendra,

Here's a formula for you to try:

=Arrayformula(IF(COUNTIF($A$2:$A$21,$D$2:$D$21)>0,"Yes","No"))

To understand how it works and build such formulas, I highly encourage you to learn how these functions work:

ArrayFormula function

IF function

COUNTIF function

Hello,

Thanks for the post - I've spent hours trying to figure out how to get the info I need. Still no luck.

I have a spreadsheet with multiple lines that contain order info (RAW). I want the formulae to specifically look for a customer name, and then the word "fruit" and bring me the info "Small" / "Large" or nothing when there is no fruit add on

=QUERY('RAW'!F:M,"SELECT M WHERE (lower(M) contains 'seasonal fruit add-on') AND ((lower(F) = lower('"&E2&"')))")

Please help :) Thank you!

Hello Dani,

The QUERY formula simply returns the contents of your table.

If you want to have special words for all cases when your criteria are met, please try using the IF function instead.

I'm doing exactly what the example is : =VLOOKUP(PersEmail!:N2,Clicked!$A$1:$A$120, 1, true) where all the refs are to sheets in the same google sheet

I've entered each of the parms PersEmail!:N2 and Clicked!$A$1:$A$120 and in other cells and I get back the expected content - a string and an array of strings

I get Parse Error

Hello Les,

Your formula has an odd colon right after a question mark in the first argument: PersEmail!

:N2. Remove it and the formula should work fine.Hi Natalia,

Thank you for this great tool! I was wondering if there was a way to have the results displayed in the same cell or a way to separate out the VLOOKUPs so that they don't overwrite the cell below when it displays the results.

Thanks,

Jon

Hi Jon,

Thank you for your feedback!

Could you please specify which of two add-ons mentioned in this blog post you mean: Merge Sheets or Multiple Vlookup Matches?

Bonjour Natalia,

J'ai la même question que Jon. Nous souhaitons savoir s'il est possible d'afficher deux recherches dans la même cellule. Par exemple dans la cellule B7 afficher un code postal suivi du nom de la ville.

Cette formule ne fonctionne pas : =VLOOKUP($C$4;'Base Client'!$1:$400;7);VLOOKUP($C$4;'Base Client'!$1:$400;8)

Est-ce un autre caractère pour séparer les deux valeurs ? "+" ou "," ?

Merci pour votre aide.

Elodie

Hello Elodie,

Please try this formula instead:

=ArrayFormula({VLOOKUP($C$4;'Base Client'!$1:$400;7);VLOOKUP($C$4;'Base Client'!$1:$400;8)})

I'm afraid it's the only way to make two VLOOKUPs work within one formula. Alternatively, you can use INDEX MATCH to pull the entire row of related records at once.