Comments on: INDEX MATCH in Google Sheets – flexible Vlookup for your spreadsheets

When you need to find data in your sheet that corresponds to a certain key record, it is usually Google Sheets VLOOKUP you turn to. But there you go: VLOOKUP slaps you with limitations almost immediately. That's why you'd better increase the resources for the task by learning INDEX MATCH. Continue reading →

Comments page 2. Total comments: 79

  1. Hello, firstly i'd like to say thank you for all the guides, they have really helped!

    So I have one sheet with 2 tabs on it. First is called "Items" and second is called "Master".
    Here is the simplest way to describe what I need:
    Reference: Items!A:A (List of unique ids)
    Look here for a match: Master!C:C (List of ids, some repeated)
    Return: Master!B:B (info to go with the ids)
    Into: Items!G:G (results need to be in one cell and separated by commas)
    The tricky thing is there may be any number of matches in Master!C:C from 0 upwards, and I need all of them except the first one since that one is different and I already have that in Items!E:E. If there are no matches it stays blank.

    I am currently using this formula to get the first match in Items!E2, but I don't know how to adapt it for the rest of the matches and exclude the first to go in Items!G2.
    =IFERROR(INDEX('Master'!$B:$B, MATCH ($A2, 'Master'!$C:$C, 0)))

    1. Hello!
      Unfortunately, without seeing your data it hard to give you advice.
      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.

      1. Sorry about that, I couldn't come up with a better way to explain (it already took me an hour to write the question the first time). It also turns out I didn't need to cut off the first match, so that made it a lot simpler and I ended up using FILTER like this instead:

        =IFERROR(CONCATENATE("{ ",JOIN(", ",UNIQUE(FILTER('Master'!$B:$B , 'Master'!$C:$C = $A2 ))," }")))

        Unfortunately I still can't figure out how to sort the results by 'Master'!$A:$A but it's getting there...
        Thank you for trying to help anyway.

        1. Hello!
          Correct your formula to write your search results on a single line.

          =IFERROR(CONCATENATE("{ ",TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Master!$B:$B, Master!$C:$C = $A2))," }")),"")

          1. Thanks, I didn't know TEXTJOIN was a thing since I always get the same result, but I guess there wil be times when it matters so it's good to know.
            Do you know how I can get the results in alphabetical order of Master!$A:A without having to sort that sheet manually?
            I can only figure out how to use SORT to get the results in alphabetical order of themselves (Master!$B:B) which is no use to me.

              1. Apologies TEXTJOIN does work when I remembered to put TRUE in. Thanks!

              2. Nevermind I figured it out. Also TEXTJOIN didn't work but JOIN does. Thanks very much for your time and trying to help though! Sorry I cannot explain things clearly.

              3. Currently the results are in the same order as they appear on the Master tab but I cannot rely on people to remember to sort that tab alphabetically by column A manually after adding things to it.
                Since I am taking results from Master column B instead of Master column A I can't figure out how to use SORT on the results without sorting them alphabetically according to column B which is not what I want. I need the results to be appear as if Master tab had been sorted alphabetically by Master column A before the formula was applied, even though it is not.

  2. I need to match with max of col index in google sheet. But couldnot able to acheive it
    Eg. Col A has 10 prod codes (may repeat many rows)
    Have to match with max of col B values respectively
    Product Code Test %
    A 50
    B 55
    A 70

    In the above case if I filter A, then the restult should be MAX(50, 70)which is 70

    1. Hello Saravana,

      If I understand your task correctly, the MAXIFS function may help you. It returns the maximum value from one column based on the condition in another column. For you it may look like this:
      =MAXIFS(B2:B10,A2:A10,"A")

      If you mean something different, please describe your task in more detail.

  3. great tutorial. thank you so much!

  4. Hello, I hope you're fine.

    I have an issue on Gsheet in a INDEX(MATCH formulae.
    First, I get value (invoice's numbers) from differents sheets with a filter formulae.
    Secondly, I have to make an INDEX(MATCH of these values with an extraction of my accounting software.

    I don't know why, the return is "Did not find value '2020026' in MATCH evaluation." but, when I use ctrl + f, I find this value in my extraction.

    For your information, my formulae is juste below :

    =IFERROR(
    IF(A2="";"";
    IF(INDEX('COMPTA 2000150200'!$A:$A;MATCH(A2;'COMPTA 2000150200'!$A:$A;0);1)=A2;"OK";"NOK"))
    ;"NOK")

    A2 was get by filter function. But when I replace "A2" by the invoice number (2020026), it's works !

    Can you help me please ?

    Thanks in advance,

    Regards.

    1. Hello Corentin,

      Please double-check the format of the values returned by FILTER and of the value in A2 which is used in MATCH. If they differ, this can cause difficulties.

      If this is not your case, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com). 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 by replying here.
      We'll look into it and see what may be causing difficulties.

  5. How to return multiple search results using index match? The Aggregate function of excel does not work

    1. Hello John,

      Do you need to return multiple matches in rows or columns? What formula are you trying to use in Google Sheets exactly?

  6. Hi I have 3 sheets

    One name "Draw Results" Contains numbers e.g. 002345
    (numbers are filled on the entire sheet e.g. 'Draw Results' A1:K159)

    Second Named "Bond List" Contains numbers e.g. 002347
    (numbers are filled on the entire sheet e.g. 'Bond List' A1:K159)

    Third Named "Matched Results" want the matched results displayed in here.
    What Formula do I put in here I have tried =INDEX('Draw Results'!$A$1:$A$159,MATCH('Bond List'!A1,'Bond List'!$A$1:$A$175,0),1)

    Please help
    Thank You

    1. Hi Frais,

      For me to be able to help you, please specify whether you're trying to compare two sheets for duplicates or find matching data between two columns in two sheets and pull the corresponding data from some other column.

  7. Help! I have two sheets: MasterSheet and Operations Sheet.
    I need to replicate Columns H and I on the MasterSheet has two columns (H and I) in the Operations Sheet in Column C (which corresponds to Column H on the MasterSheet) and Column D (which corresponds to Column I on the MasterSheet).
    I need a formula where Column D will automatically populate with the cell value in Column I on the MasterSheet if the value in Column C matches the value in Column H of the MasterSheet. Essentially, if Column H and C are the exact match, then Column D should populate with the value in Column I in the same Row as Column H.

    1. Hello Elsa,

      I'm really sorry but your description is not clear.
      For me to be able to suggest you anything, please try to specify (consider bulleted list) what columns each sheet contains exactly and what data should be pulled and where exactly. Thank you.

      1. I have a Worksheet with 2 tabs.
        The first tab is named MasterSheet.
        - Column H heading on the MasterSheet is called "Purchase Req"
        - Column H on the MasterSheet is called "Purchase Order"
        The second tab is named OperationsSheet
        - Column C heading on the OperationsSheet is called "Purchase Req"
        - Column D heading on the OperationsSheet is called "Purchase Order"
        I need a formula for Column D on the Operations Sheet that:
        1) Searches column C on the Operations Sheet for an exact match as that in Column H of the MasterSheet
        2) If it finds the exact match, Column D in the OperationsSheet will then return (or copy) the value found in Column I of the MasterSheet.
        Example:
        MasterSheet OperationsSheet
        H I C D
        PR PO# PR PO#
        1234 450001234 6738 450089628
        6738 450089628 1234 450001234

        1. Thank you very much for the detailed explanation, Elsa!

          Now I've got your task. Here are the formulas for you to try (assuming there are 10 rows in a table, so please adjust the number 10 to your number of rows):

          1. If you want to use INDEX MATCH from this blog post:
            =INDEX('Master Sheet'!I1:I10, MATCH(C2, 'Master Sheet'!H1:H10, 0))
          2. A version with VLOOKUP that will fill all rows in a column automatically at once:
            =ARRAYFORMULA(VLOOKUP(C2:C10, 'Master Sheet'!H2:I10, 2))
            Number 2 here is the number of the column I in my small table. Please also change it to yours.

          Hope this helps!

          1. Thank you, this is very helpful! One last question, how do you add to the first formula to replace the #N/A error with a blank cell if the searched value is not found?

  8. Hi Sir

    Thanks for your very usefull tutorial.

    I need your help in very small thing. I am converting a simple formula into array formula but its not working form me for some reason.

    Normal formula is =IF(AND(A3<'IIR & NPV - Calculation Dashboard'!$B$2,OR(A3=1,A3=13,A3=25,A3=37,A3=49)),'IIR & NPV - Calculation Dashboard'!$B$12,0)

    Its Working as expected for me. But when I am converting it into Array formula, it looks like this

    =ArrayFormula(IF(AND(A3:A<'IIR & NPV - Calculation Dashboard'!$B$2,OR(A3=1,A3=13,A3=25,A3=37,A3=49)),'IIR & NPV - Calculation Dashboard'!$B$12,0))

    It is not giving me the same result as its normal formula.

    Can you please help me out here and tell me what is the error in this array formula?

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =ArrayFormula(IF(IF(A3:A20 < $B$2,TRUE,FALSE) * (IF(A3:A20=1,TRUE,FALSE)+ IF(A3:A20=13,TRUE,FALSE)+ IF(A3:A20=25,TRUE,FALSE)+ IF(A3:A20=37,TRUE,FALSE)+ IF(A3:A20=49,TRUE,FALSE)),$B$12,0))

      Please adjust this formula according to your data if needed.

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