Forum Discussion

certavi's avatar
certavi
Copper Contributor
Feb 22, 2020
Solved

INDEX MATCH function, avoid duplicate returns

Hi everyone!   I'm working on a sheet for work. I am on my last task and can't seem to figure this one out. I'm hoping someone can help:   I have a data sheet with all the data. In my current she...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 22, 2020

    certavi 

    If you are on Excel with dynamic arrays that could be

    =UNIQUE(FILTER('Horse data'!B2:B100,
  (I$13='Horse data'!C2:C100) * ($K$13='Horse data'!D2:D100)
))
SergeiBaklan's avatar
SergeiBaklan
MVP
Feb 22, 2020

certavi 

If you are on Excel with dynamic arrays that could be

=UNIQUE(FILTER('Horse data'!B2:B100,
  (I$13='Horse data'!C2:C100) * ($K$13='Horse data'!D2:D100)
))
cweaver345414's avatar
cweaver345414
Copper Contributor
May 01, 2022

SergeiBaklan  im having a similar issue with an excel formula. When I drag this down, I am having duplicate returns until I another value hit. Do you know how the formula should look to delete the duplicates?

 

=INDEX('Walleye OMS'!B2:B303,MATCH('final (2)'!A3,'Walleye OMS'!U2:U303,0))

 

 

 

  • Brad Yundt's avatar
    Brad Yundt
    MVP
    May 01, 2022

    cweaver345414,

    MATCH always returns the first match for the item sought. That's why it keeps duplicating the value returned.

     

    I assume that you are copying the formula down in worksheet final (2) starting in row 3.

     

    =INDEX('Walleye OMS'!B$2:B$303,AGGREGATE(15,6,(ROW('Walleye OMS'!U$2:U$303)-ROW('Walleye OMS'!U$2)+1)/('Walleye OMS'!U$2:U$303='final (2)'!A3),COUNTIF('final (2)'!A$3:A3,'final (2)'!A3)))

     

    Brad

    • Sean_Sangreal_Ang's avatar
      Sean_Sangreal_Ang
      Copper Contributor
      May 08, 2023

      Brad YundtHi Brad, i would love to have your advice on a similar matter. There are only 2 sheets to look at, "Summary" and "Startup". So essentially on the summary page, i want to reflect the names of companies who have Type A in column B, titled Product Category. I want the names to be reflected in sequence.

      So on the startup sheet,
      1. Brimstone, Type A
      2. Carbicrete, Type C
      3. Fortera, Type A
      4. Solidia, Type A
      5. Biomason, Type A
      6. CarbonCure, Type C

       

      This is my code. I'm stuck at how to get the names to not be duplicated at the first match, i want it to go down the list.

      My code is =INDEX(Startup!A:B,MATCH($R$9,Startup!B:B,0),MATCH($Q$10,Startup!A:A,0))

       

       

      Apologies for not attaching the file, i currently do not see a button to do so. Hence, i've taken the liberty to provide you with the 2 pics above, hoping that it can help you in manipulating my formula.

      Many thanks.

       

      • byundt's avatar
        byundt
        Copper Contributor
        May 08, 2023

        If you click the link for "Open full text editor" at the bottom of the Reply box, it will give you an icon for attaching files.

      • cweaver345414's avatar
        cweaver345414
        Copper Contributor
        May 01, 2022

        cweaver345414 Brad Yundt 

         

        Hi Brad, it looks like im running into a similar issue but on a different spreadsheet. im trying to grab all equity quantities and put it on a separate spreadsheet. Do you by chance have any idea what I may be missing?

         

        =INDEX('Walleye JPMcopy)'!L$2:L$643,AGGREGATE(15,6,(ROW('Walleye JPMcopy)'!D$2:D$643)-ROW('Walleye JPMcopy)'!D$3)+1)/('Walleye JPMcopy)'!D$2:D$643="EQUITIES"),COUNT('Walleye JPMcopy)'!D$3:D3,'Walleye JPMcopy)'!D3)))

         

         

         

Resources