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$14). 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$14,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. If this does not help, check other possible reasons why VLOOKUP fails.
  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$13,2,false)

The formula will search for the value in A2 in the range A2:A13 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$13,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$13,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$13,2,false)

To pull the amount: =VLOOKUP($F$1&"*",$A$2:$C$13,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:G14 (lookup_range) and return a match from F3:F14 (return_range), use this formula:

=INDEX($F$3:$F$14, MATCH (A3, $G$3:$G$14, 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:G14 and the return range is F3:F14, the formula goes as follows:

=ArrayFormula(INDEX($F$3:$F$14, MATCH (TRUE,EXACT($G$3:$G$14, 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:

Google Workspace Marketplace badge

Once the add-on is added to your Google Sheets, you can find it under the Extensions 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 Extensions > 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. And Update your main table right where it is: Select one or more additional options.

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

Video: How to use Merge Sheets to vlookup without formulas


Vlookup multiple matches an easy way!

Filter & Extract Data is another Google Sheets tool for advanced lookup. 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 Filter & Extract Data add-on and get a flawless formula in seconds!

Google Workspace Marketplace badge

In your Google Sheet, click Extensions > Filter & Extract Data > Start, and define the lookup criteria:

  1. Select the range with your data (A1:D15).
  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 Filter & Extract Data home page or get it now from the Google Workspace Marketplace:

Google Workspace Marketplace badge

Video: How to vlookup multiple matches using the add-on


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

Spreadsheet with formula examples

Sample Vlookup Spreadsheet

Table of contents

144 comments

  1. Hi!

    I'm really struggling to link numbers with drop down options and i'm pretty sure I need to use VLOOKUP. I need on one sheet when each drop down is chosen to pull through how much it is for that chosen option.. So when 'lasagne' is chosen on the drop down on Sheet 2, it pulls the total price for Lasagne through from Sheet 1 and places it on Sheet 2, then changes depending on the drop down chosen.. Does that make sense? I'm not sure if it is something you can help with?

    • Hi Jennifer,

      Yes, it looks like VLOOKUP or INDEX/MATCH should help you with the task. Have you tried building the formula following the tutorial? What difficulties have you faced?

  2. Hello,

    I need to find the value before multiple blank cells:

    Example:
    Monday
    a
    b
    c
    Blank cell
    Tuesday
    d
    e
    f
    g
    Thursday
    h
    i
    j
    blank cell

    I need a formula for Monday which will give: c. Another one which for Tuesday will give g. For Wednesday: N/A, For Thursday: j

    Thank you

    • Hello Priya,

      Try this formula for Monday:
      =IFERROR(INDEX(A1:A13, MATCH("Tuesday", A1:A13, 0) - 1), "N/A")

      It returns the last cell belonging to Monday – the one before Tuesday.

      To find values for other days of the week, change Tuesday respectively.

      You will learn more about INDEX MATCH in this blog post.

  3. A B
    1 GFWM 34.2
    2 GFWW 37.0
    3 GBWM 34.4
    4 GBWW 37.2
    5 GFRM 32.2
    6 GFRW 34.8

    9 GBWM =LOOKUP(A9,A1:A6,B1:B6)

    I was hoping to get 34.4 as a result but get #N/A (Did not find GBWM in LOOKUP evaluation).

    Can you help?

    Thank you.

    • Hello Michael,

      As far as I can see, you use LOOKUP, while the function you need is VLOOKUP. Please review the fust 2 points of the article above to build the correct formula.

  4. Hi Natalia,

    Your Blog has been very helpful , . Although I am having problem in google sheets .
    and my Vlook up is not returning Right Date .

    =TEXT(VLOOKUP(A2,MIM!AC:AM,9,FALSE),"MM/DD/YYYY")

    Column (9) with return date return values.

    Please help me

    • Hi Abegail,

      Please consider sharing an editable copy of your spreadsheet along with your formula with us (support@apps4gs.com).
      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 it.

  5. I have 2 worksheets
    worksheet A
    worksheet B

    I want to check if a value in a column exists in another column in another worksheet using data validation. I have been trying to solve it using vlookup with importrange. How can I do it.

    • Hello Rebecca,

      For me to be able to help you better, please specify what the result should show. Would you like a check mark if the value exists in another sheet? Or some text with a hint that it's a duplicate? Should it be in the same cells where you enter the values or in another column?

  6. Hi,

    As I look at the above examples in vlookup of two google sheets, I haven't seen the same as what I want my google sheet to be happen.

    I have a "Form Responses", the most important for this sheet is the Mem_ID, Month and Amount.

    In my other sheet "Sinking Fund 2023", in Members Contribution, I am creating the formula under "Jan1st, Jan2nd, etc." and to be looked up from Form Responses.

    If MemID_2022-0001 has a response for Jan1st, the amount should be added in Jan1st column and if it is Jan2nd this should be automatic added in Sinking Fund 2023 google sheet.

    Any one knows how to do the formula. This also needs the importrange function since we are using 2 google sheets.

    Below are the links of my google sheet.

    Deposit and Payments
    https://docs.google.com/spreadsheets/d/124atlHALikF_sUt7JhV7WvpZwH7MokTDgdWckip_vPE/edit#gid=1642645643

    Sinking Fund 2023
    https://docs.google.com/spreadsheets/d/1L6XoVKF6VlwV6Ia7cDj4Ta5cz3nXDuhjoSi0Nwjhy5E/edit#gid=1548046258

    Thanks in advance

    • Hi Richard,

      You need to use QUERY function for this task. Put the following formula into E4 of Members Contribution, Sinking Fund 2023:
      =QUERY({IMPORTRANGE("LINK_TO_Deposit_&_Payments_FILE","Form Responses!$I$1:$L")},"select sum(Col4) where (Col1='"&$B4&"' and Col2='"&$E$3&"') label sum(Col4) ''" ,0)

      For it to work, don't forget to replace LINK_TO_Deposit_&_Payments_FILE with the link to that file and connect the IMPORTRANGE to it as well. Here's a tutorial on the IMPORTRANGE just in case.

  7. Good day, Svetlana!

    I am creating an Inventory Management on Google Sheets which has 2 main sections- RECEIVED ITEM and SOLD ITEM. Both of these sections contain Item description, Lot No. and Expiry. Each item received has its own unique lot no. and expiry although there are instances that some items have the same lot no. and expiry. I have created a data validation for all columns in the SOLD ITEM section based from the data encoded in the RECEIVED ITEM section, which looks like this (sample only) :

    RECEIVED ITEM SOLD ITEM
    Item Description Lot No. Expiry Item Description Lot No. Expiry
    Apple 1546 Oct 23,2023 Apple
    Lemon 3479 Jan 5, 2024
    Banana 1546 Jan 5, 2024
    Grapes 6873 Feb 4, 2023
    Apple 7490 Nov 11, 2024

    My question is: how do I create a data validation dropdown where the dropdown list would only show the lot no. and expiry of the items i have sold?
    Say I have sold Apple, when I click the dropdown in the Lot No. column, I'd like it to only give me 2 choices based from the data encoded in the RECEIVED ITEM which are 1546 and 7490. Same goes with the Expiry which should only show me Oct 23, 2023 and Nov 11, 2024. Coz as of now, if i click the dropdown for lot no. and expiry in the SOLD ITEM, my actual sheet would show all the lot nos. and expiry for all the items encoded in the RECEIVED ITEM section which is confusing coz we have numerous items.

    I hope this is possible coz this is the only problem left for me to solve :)

    Thank you in advance!

    PS.

    I have omitted some columns like date, qty, customer name and u/m which I think is not important with my problem.

    • Hello Svetlana,

      Sorry, it's my 1st time to post here. Seems my sample's format did not appear as i have typed it. But I do hope you get what I am trying to solve here :D Thank you!

      • Good day, CheL!

        Please consider sharing a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a source data (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. The result sheet is of great importance as it gives us a better understanding than any text description.

        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 it and try to help.

        • Good day, Natalia!

          So glad to get a response from you.

          I have already shared the file and I hope you could help me solve 2 things in the Sold Items Section of the Stock Card (col J-V):

          Say if I sold Lifo Auffer 480ml, I want to achieve the ff results:

          1. I want the dropdown on column S (Lot No.) to only show me all the Lot Nos. for Lifo Auffer 480ml which I have encoded in column G (source data)
          2. I want the dropdown on column T (expiry) to only show me all the expiry dates for Lifo Auffer 480ml which i have encoded in column H (source data)

          It would be a great relief if you could help me with this!

          Thank you!

          • Hi CheL,

            There's a solution in your spreadsheet, please take a look.

            I had to add an extra sheet (LOT filters) where I used FILTER function to collect lot numbers from column G based on the description values.
            The FILTER formula had to be transposed and copied to the rows below: each formula collects the numbers for the next value from column O. Just play around with filling in your table and see what appears on that extra sheet.

            Thus, each data validation in your column R references the correct values.

            I believe once you review this solution, you'll be able to create the same for your EXPIRY column.

            Hope this helps!

  8. Multiple Vlookup matches looks great but the add-on required that I give you the ability to delete my sheets. That is a non-starter and shouldn't be necessary

    • Hi!
      Multiple Vlookup matches creates a new dataset in the location of your choice according to your requirements. It does not require any data to be deleted. It is possible that the range where you want to put the data is not empty.

  9. I have to look up multiple employees numbers several times a week, however; it's usually the same employees. I would like to use sheet 2 for listing the employee number, last name, first name, then use this list as a lookup on sheet 1 by typing the last name and first name, with the employee number auto-populating the first column. How would I write this formula? Thank you.

    • Hello Julia,

      For me to be able to help you better, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with a description of your task.
      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.

      In the meantime, you can try our Multiple VLOOKUP Matches decribed in this section, it will build the formula for you.

      • I have shared a sample spreadsheet. I hope I did it correctly. Thank you for any assistance you can give me.

  10. I have an odd inquiry.
    I want to create a drop down list in column D.
    This based on column C.
    The drop-down list I need is a vlookup pulled from tab 2 referencing the data key in column c of tab 1.
    Tab 2s data is pulled though through columns C:F.
    So the drop down I am after is based on tab 1 column Cs key, referencing data matching Cs key in Tab 2, only need data choices of C:F. Is this a thing I can do with data validation and vlookup or am I looking to learn the programming aspects to get this done?

    • Hello Casey,

      For me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) including the sheet with the result you'd like to get. 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. I want to use "vlookup" and "value by colour" at the same time in a Formula on different sheet. How can i make it?

  12. I am trying to pull the email from a list where the info is written: last name, first name.
    And on my search cell is: first name, last name.
    Can I do that with Vlookup? or what formula can I use? Do the names have to be written precisely in the same order? Thank you

    • Hello Vanessa,

      Yes, formulas in Google Sheets work with complete matches unless you use regular expressions or wildcard characters. But if you refer to a cell as a sample, the formula will be looking for that exact record with words in that exact order.

      To quickly bring those names in the same order, you can split them by comma & space to columns, change columns places, and merge them back by comma and space.

  13. I'm not able to vlookup from one sheet to another Google sheet what to do

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

    • Hello Guenahel,

      If I understand your task correctly, try this formula in C3 and copy it down the column:
      =IF(General!E2=TRUE,IFERROR(VLOOKUP(A3,General!$B$2:$M$13,3),"no matches"),"")

      To understand how this formula works in order to build similar formulas for other columns, please visit these tutorials:
      Google Sheets IF function
      IFERROR + VLOOKUP

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

      • 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

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

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

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

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

    • Hi Carl,

      Please specify what issues you're getting. Is there an error? If so, please specify what it says and what you see when you hover your mouse over the error.

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

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