Comments on: Google Sheets VLOOKUP with examples

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. Continue reading →

Comments page 3. Total comments: 144

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

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

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

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

      1. Yes Sir your advice will helpful for only maria not for everyone

        1. If the suggested formula doesn't work for your case, feel free to describe it in detail. We'll try to help.

  3. I find it nothing special

    1. Thank you for your feedback, Vuitton.

      If you were looking for something particular in this blog post, you can describe your task and we'll see if we can suggest a solution.

      1. please search for solution ( sir Alexandr) mention the problem you only 5 day for this problem solution

        1. I'm sorry, it's not clear what you mean. Do you need a solution for your task? Can you describe it in detail?

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

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

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

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

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

  6. 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!

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

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

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

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

    1. 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! :)

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

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

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

    1. Hello Redd,

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

  12. may I know how to vlookup in different forms?

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

    1. Same struggle im facing this 2021, any updates?

    2. 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:C6 can be your entire table and A, B, C are columns to return whenever NAME appears in column A.

      We haven't described QUERY in detail on our blog yet, so you can learn the function here.

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

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

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

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

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

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

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

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

  20. 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!

  21. Hi

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

  22. I still don't get it

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

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

  24. Thank you for this post!! Super helpful!!

  25. Great post - thanks!

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

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

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

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

  26. Thank you guys,

    This post is the best!

  27. Svetlana
    Your Great
    Thanks countless

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