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 the lookup column is sorted (TRUE) or not (FALSE). In most cases, FALSE is recommended.

  • If is_sorted is TRUE or omitted (default), the first column of range must be sorted in ascending order, i.e. from A to Z or from smallest to largest.

    In this case a Vlookup formula returns an approximate match. More precisely, it searches for exact match first. If an exact match is not found, the formula 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, an #N/A error is returned.

  • If is_sorted is set to FALSE, no sorting is required. In this case, a Vlookup formula searches for exact match. If the lookup column contains 2 or more values exactly equal to search_key, the 1st value found 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 things to 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, remember to sort the first column of range in ascending order. In this case, the VLOOKUP function will use a faster binary search algorithm that correctly works only on sorted data.
  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_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:
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: formula-free alternative for Google Sheets Vlookup

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

Vlookup multiple matches an easy way!

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:

  1. Select the range with your data (A1:D9).
  2. Specify how many matches to return (all in our case).
  3. Choose which columns to return the data from (Item, Amount and Status).
  4. 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.
  5. Select the top-left cell for the result.
  6. Click Preview result to make sure you will get exactly what you are looking for.
  7. If all is good, click either Insert formula or Paste result.

Vlookup multiple matches in Google Sheets

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:
The formula to return multiple matches in Google Sheets

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!

You may also be interested in

96 comments to "Google Sheets VLOOKUP with examples"

  1. Guenahel says:

    I'm trying to see how I can workout the Vlookup function and IF statement with my spreadsheet
    I Want column "C" from the "Summary" Sheet pulling data "General" Sheet
    This is my initial formula =VLOOKUP(A4,General!B2:M13,4)
    Im trying to search that matches the Column "Summary A" on column "General B"
    When it matches, I want this to get the VA on column " General D" only if column "General E" has a check or TRUE value.

    https://docs.google.com/spreadsheets/d/1XgRyASr8alEB0AT36KbYNDj1XVb7kTM1sdvTOmWy10k/edit#gid=134747200

  2. Charlie says:

    I'm trying to Search Column C but for the results of searching, Column C to include Column D in the displayed result.

    ///-----------------------------------------------------------------------------------------------//
    /// Column A | Column B | Column C | Column D //
    1// Search_"Cool" | |Cool Statemnt | Cooler Statmnt //
    2// Cool Statemnt| Cooler Statmnt| | //
    ///----------------------------------------------------------------------------------------------//

    The formula I am using is =ArrayFormula(IFERROR(VLOOKUP($A$1,$C$1:$D$79,{1,2}), "NOT FOUND"))

    The problem I'm running into is that regardless of what I search, I can only get two results from Column C, and its matches Column D for results. Though I have 75 Column C, word phrases I'm only getting $C$67 or $C$1. Of course NOT FOUND shows.

    Thoughts and thanks in advance!
    Charlie

    PS, I gave access to the email stated below.

    • Hello Charlie,

      Thank you for sharing your spreadsheet right away.

      Your VLOOKUP on the "Sayings" sheet doesn't work because it looks for the exact match: cells that contain only the word from A2. To look up for cells that contain this word, you need to build VLOOKUP with wildcard characters. I've entered the example to A5.

      Also, VLOOKUP returns only the first matching record. To lookup for multiple matches, you will need to use the method described in this part of the tutorial.

      • Charlie says:

        Hi Ablebits,

        THANKS! I figured it had to do a little with the vlookup and a little with my formula! It's amazing how something so small "*"& can make such a huge difference!

        I can personally say, Ablebits helped me in a quick and professional manner, I would highly recommend them as they are quick, concise, and responsive!

        Greatly appreciate the help.

        - Charlie Mason

  3. William Ramos says:

    Hello,

    I'm trying to run a weekly Pick'em type game for a group of people and I run it so whoever got the max points for the week gets awarded them 1 playoff bracket. I would like to sum these playoff bracket so people can know how many they get. Currently, each week is separated into separate sheets and then the total playoff brackets is summed in a separate summary standing sheet with this code:

    =IFERROR(VLOOKUP(B3,'Week 1'!B$4:D$43,3,FALSE),0)

    Is there a way to automatically add new sheets to this so it sums the playoff bracket total? Or do I have manually add it every week like this?

    =SUM(IFERROR(VLOOKUP(B3,'Week 1'!B$4:D$43,3,FALSE),0),IFERROR(VLOOKUP(B3,'Week 2'!B$4:D$43,3,FALSE),0))

    Thank you and have a great day!

  4. Kyle Allison says:

    I can get Vlookup to get the 1st set of values I desire, but my next set is more difficult. I want my next Vlookup to bring me the next unique combination from the data set not just the 2nd or 3rd value.

    Table 1
    Name Product
    John Apple
    John Apple
    Ken Apple
    Ken Orange
    John Orange

    Want
    Table 2
    Name Product
    John Apple
    John Orange

  5. Doug Smith says:

    Good morning

    I am wondering if vlookup can look up 2 numbers on the same line but different columns and then display a column on that line.

    I make billing sheets with a job number and date in two different cells.
    The job number uses vlookup to pull descriptions from the main job number sheet.
    The date is auto populated from the main timesheet with everyone's hours.
    What I would like to do is have a cell on each of the billable sheets have a unique invoice number.
    I do this by hand now but i have made mistakes and would like it to autofill
    On the invoice sheet I have unique numbers and then a job number(may span many weeks) and the end of week date. I would like to take the invoice number that is on the line that matchs the job and date of the billing sheet and populate the top right cell with the unique invoice number.
    I was trying to get vlookup to do it but I dont think it will.
    Can you recommend something please.

    thanks for your time

    • Hello Doug,

      For multiple criteria, you can try using INDEX MATCH instead.

      In case you're still not sure about the solution, consider sharing a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  6. Carl says:

    Hi!

    Thanks for a great support.
    Unfortunately I´m getting something wrong and I´ve been struggling for a while now...

    In cell B2:
    =VLOOKUP(A2,'Excellistor'!$A$1:$B$7,2,false)

    A2 consist of a name

    B2 will show surname

    A separate tab (Excellistor) consist of a matrix (A1:B7) with names and surnames.

    Why do I get issues?

  7. SD says:

    I have 2 tablets 1st is reference and second output required

    1st table is like

    Date Product Price
    1-1-20 ProductA 1000
    1-1-20 ProductB 1100
    4-1-20 ProductA 1010
    6-1-20 ProductA 990
    Contains 25k rows

    Second Table

    Date Product Price Available
    1-1-20 ProductA 1000
    3-1-20 ProductB 1100
    4-1-20 ProductA 1010
    5-1-20 ProductA 1010
    7-1-20 ProductA 990
    Contain 60k row

    Currently i am doing this with index, match with maxifs. But i need arrayformula for google sheets....

    Kindly help...

  8. SD says:

    I have a data sheet like below

    Date product price
    1-1-20 product a 1000
    1-1-20 product b 1100
    4-1-20 product a 1010
    And so on and the row is about 25k

    I have another sheet where I want to get the price as per product and date

    Date Product Price Applicable
    2-1-20 product a 1000
    2-1-20 product b 1100
    4-1-20 product a 1010
    7-1-20 product a 1010

    Above table output is required for price applicable.

    Although I am doing it with index, match and maxifs. But I need array formula.... Plz help

  9. Elmar says:

    Awesome blog article! Advertisement for Power Tools at the bottom of the page is incredibly annoying though. and no way of closing it.

  10. Peter says:

    I'm trying to take the inputted data from one sheet, Column A, and have it also end up in a second sheet in Column X. The second sheet is sorted by a Building # and then Unit #. The first sheet has these columns as well. And they are Column C (Building) and Column D (Unit #). The first sheet won't be in any particular order but does have 2 columns that are in both sheets. I'm not sure if a VLOOKUP formula would work.

    Any assistance is greatly appreciated.

    Peter

    • Hello Peter,

      If VLOOKUP doesn't work as you expected, please consider sharing a small sample spreadsheet with us (support@apps4gs.com) with 3 sheets: (1-2) a copy of your source data (3) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task and try to help.

  11. Sarah says:

    Hi, I want to ask you, I have two tabs in one google sheet. I want to make if the item in the first tab match with the second one , then it shows " 〇 " and if it's not it shows " × ". How can I do that? Thank you. I hope you can answer me. Have a good day !

  12. Sumit Sarkar says:

    It is not returning the 36th or more column while using vlookup in google sheets from another different sheet. And giving error (Range is too large). Can anybody help?

  13. Refi says:

    So i want to use vlookup in multiple sheets with in a single spreadsheet in google sheet. I want to use a data validation drop down to switch search range (array). The Data validation Dropdown contains names of N number of sheets present in a spreadsheet. So when I change drop done selection the range in the vlookup changes accordingly. Is it possible?
    =Vlookup(A1,searh_range,2,0)
    In this formula how can I have search_range change according to the selection I make from drop-down list.

  14. Daniel says:

    When using this for a Google Sheet that is active for form responses, is there a way to fill the cells with the VLOOKUP value upon submission? Also, is there a way to remove the #N/A for the column for cells that are currently unpopulated?

  15. Alex says:

    Hi Ablebits Team,

    Just wanted to say thank you for writing this post. It was extremely helpful and within ten minutes of reading this article I was able to adjust the functions in my spreadsheet to properly reflect the data I needed it to show. Sending good vibes :)

  16. Abbie says:

    Hi, this is very helpful however is it possible to combine this formula with the MAX and Array function?

    =ARRAYFORMULA(IF(A3:A="",,IFERROR(VLOOKUP(A3:A,'Review info'!$A$2:$BN,{66},FALSE))))

    This is my formula, however I want to return the last occurrence, which will be a date.

    Thank you!

    • Hi Abbie,

      Please consider sharing an editable copy of your spreadsheet along with the formula you're trying to use with us (support@apps4gs.com). If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.
      I'll look into your task and try to help.

  17. Madhav says:

    Thanks a ton man. The way you have explained VLOOKUP above is too on point and too easy to understand. Keep up the good work mate. Cheers :)

    • Hello!
      Sorry, it's not quite clear what you are trying to achieve. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  18. Wayne says:

    Hello all,

    Slightly different, but I have been trying to add a number of vlookups to give a total eg...

    =VLOOKUP($B$10,Master_Lookup!$A$2:$J$20, 8, false) * C28+VLOOKUP($B$10,Master_Lookup!$A$2:$J$20, 9, false) * D28+VLOOKUP($B$10,Master_Lookup!$A$2:$J$20, 10, false) * E28

    Why does it only let me have a string of 3? Typical, I would want 4!
    The reason for this is I have a series of prices for components against a product, and once I have the totals of the components in the spreadsheet, I want to return the total.

    Thanks

    Wayne

    • Wayne says:

      Seems I have got it working, not sure how - $A$2:$Z$20 changed to $A$2:$Z$20 seems to have done the trick - I am sure there is a much better way of doing this formula?

      • Hello Wayne,

        I'm afraid we're not aware of any limit for the number of VLOOKUPs available for summing. Can you specify what error your formula returns exactly? Please hover your mouse over it and see what its description says as well.
        Also, please try to separate the formula into 3 individual VLOOKUPs and see if they still work.

  19. Ranganathan says:

    I have 2 Sheets
    1 has all the Orders that I have received with fields such as

    Sheet 1 has (A) Order No, (B) Customer Name, (C) Phone No, (D) SKU No, (E) Category, (F) Availability
    where 10001 to 10010 Cu1, Cu2... Cu10. 1, 2, 3, ...10, SKU1 to SKU10, C1... C10, Yes or No
    Sheet 2 contains the same set of fields but only a Single Category Item, eg : C3 with totally 5 SKUs on 5 Rows,
    Based on the inputs of sheet2 in the availability (F) column as Yes or No, the same value has to be passed on to the identical field on sheet 1

    In other words identical data of a single row on two different sheets will have different row numbers,
    so => take the input from
    Sheet2 - Column G, Row no 3 's data and post it on
    Sheet1 - Column G, Row no 1 where both the rows are identical

    Can you please help?

    • Hello Ranganathan,

      I was trying to recreate the table by your description but a few moments confused me. I'm not sure if C3 on Sheet2 repeats for all SKUs or there are empty cells. Also, you listed 6 columns (A-F), and then mention column G.

      So, for me to provide a solution, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: Sheet1 - your source table, Sheet2 - the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm in this message thread. Thank you.

  20. Anonymous says:

    The coverage of the is_sorted attribute is highly inaccurate and misleading.

    1. The purpose is **NOT** for exact-match searching. It may be a coincidental side-effect; but that is **NOT** it's purpose. Failure to understand that leads to inaccurate usage.
    2. When is_sorted is TRUE (whether explicit or by default), you are telling the spread sheet it **may** search using a faster searching algorithm; and it will go ahead and do so. But for the algorithm to work correctly, the data **MUST** be sorted.
    3. In a roundabout way, your advice avoids the mistake of a search range that is not suited to the is_sorted setting. But the advice is still inaccurate and leads to other mistakes, misunderstanding and inefficiencies.
    4. The reason the function may return incorrect results when the data is not sorted, has nothing to do with the existence of exact matches. The is_sorted option permits the function to use a binary search to find the item. It repeatedly splits the data in half, narrowing down where it expects to find the item. But if the data is not sorted, then the lack consistent ordering means the half discarded at some point might coincidentally contain the desired item.
    5. It's inaccurate to state that is_sorted returns an "approximate match". You do at least clarify that it returns the closest match less than or equal to the search value. But it is wrong and misleading to call this an "approximate match".
    6. The reason is_sorted can return a result less than the search value is that there are cases where this is useful (such a searching for the most recent log entry at a point in time). Note that it is easier to discard a non-exact match result in cases where it's not needed, than it is to _magically produce_ the non-exact match in cases where **nothing** is returned.
    7. Conversely if the search range is **not** sorted: a nearest match is meaningless. So when is_sorted is FALSE, then nothing is returned if an exact match is not found.
    8. Relying on the side-effect of the previous point to find exact matches just prevents use of the faster binary search algorithm. It is slow and inefficient if you can simply guarantee your data is sorted. You won't notice on small sheets' but this sort of mistake quickly gets out of hand as you get more data.

    It is unfortunate that the function doesn't include an option to return exact-matches only, even when is_sorted is TRUE. But this can be done by searching once to find if the exact-match key exists; and then lookup to retrieve the desired value. (Exercise left to you.)

    • Thank you for your detailed and thoughtful feedback!

      Since this tutorial is for beginners, our main focus is on simple practical things (how to configure the arguments and what you will get as the result) rather than on the underlying algorithms. Nevertheless, I've added a brief note about a faster binary search algorithm on sorted data. More details the readers will find in your comment :)

      As for approximate match, this term is inherited from Excel VLOOKUP for consistency reasons, since many users migrate to Google Sheets from Microsoft Excel.

  21. Maria says:

    Hi,
    I managed to do the vlookup from one tab to another.
    But how do I get it to return Yes or No?
    I am looking up from a long list in another tab, and if it is a match, I would like it to say Yes.
    Thanks.

    • Hello Maria!
      You can use something like the following VLOOKUP formula. It does not extract a value from the range A2:A7, but indicates whether a match is found.

      =IF(NOT(ISERROR(VLOOKUP(E2,$A$2:$A$7,1,0))),"Yes","No")

      I hope my advice will help you solve your task.

  22. Jon says:

    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

      • Elodie says:

        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.

  23. Les Dunaway says:

    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

  24. Dani says:

    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!

  25. László Várkondi says:

    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

  26. Sydney Kinglsey says:

    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?

  27. Anthony James Murphy says:

    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

  28. Oscar says:

    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.

  29. Elie says:

    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)

  30. Janardhana J says:

    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

  31. Gail says:

    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.

  32. Brenda says:

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

  33. Jenny says:

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

  34. Mark Precious says:

    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!

  35. Ann says:

    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 -

    • Aksana (Ablebits Team) says:

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

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

      • Greg says:

        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?

        • marcw says:

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

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)