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

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

      The problem gets complicated in Excel 2007. To simplify it, I used an auxiliary column, and array-entered the following formula in cell L2, then copy it down. The formula returns the values from column B that satisfy the two criteria.

      =IFERROR(INDEX('Horse data'!B$2:B$100,SMALL(IF(($I$13='Horse data'!C$2:C$100) * ($K$13='Horse data'!D$2:D$100),ROW('Horse data'!B$2:B$100)-ROW('Horse data'!B$2)+1,""),ROWS(L$2:L2))),"")

        The next step is to avoid duplicate returns. I did that by copying down another array-entered formula in column M:

      =IFERROR(INDEX(L$2:L$100,SMALL(IF(IFERROR(MATCH(L$2:L$100,L$2:L$100,0),0)*(L$2:L$100<>"")=ROW(L$2:L$100)-ROW(L$2)+1,ROW(L$2:L$100)-ROW(L$2)+1,""),ROWS(M$2:M2))),"")

       

Resources