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
Diamond Contributor
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)
))
  • Hetz6366's avatar
    Hetz6366
    Copper Contributor
    Dec 27, 2024

    I am having the same issue with duplicate returns in my formula, how can I avoid the duplicates? I added a countif and that does not work either. Any help would be greatly appreciated. =IFERROR(INDEX('Merch Square Formers'!$A$3:$A$213, SMALL(IF((D$2<$C$2)*('Merch Square Formers'!$C$3:$C$213=C$2)*('Merch Square Formers'!$C$3:$C$213<>"")+(($D$2=$C$2)*('Merch Square Formers'!$D$3:$D$213<>"")*('Merch Square Formers'!$C$3:$C$213=C$2)*('Merch Square Formers'!$C$3:$C$213<>""))*(COUNTIF($D$4:D4, 'Merch Square Formers'!$A$3:$A$213)=0), ROW('Merch Square Formers'!$C$3:$C$213)-MIN(ROW('Merch Square Formers'!$C$3:$C$213))+1), ROW()-ROW($D$4)+1)), "")

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

         

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

    SergeiBaklan Yes, that works great! Thanks a lot!

      • Yunita710's avatar
        Yunita710
        Copper Contributor
        Oct 06, 2021

        Hello, is there a way to tdo this in excel 2007 version? SergeiBaklan 

Resources