Forum Discussion
INDEX MATCH function, avoid duplicate returns
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) ))
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.
MATCH always matches the first match. That's why you are only getting Brimstone. You need to use AGGREGATE instead.
Try copying down this formula in cell R10. I restricted it to 1000 rows arbitrarily--change that bit to exceed the amount of data you expect. Older versions of Excel bog down when you perform array calculations on full columns. The latest version of Microsoft 365 is smart enough to avoid that issue.
=IFERROR(INDEX(Startup!$A$1:$A$1000,AGGREGATE(15,6,(ROW(Startup!$B$1:$B$1000)-ROW(Startup!$B$1)+1)/(R$9=Startup!$B$1:$B$1000),ROWS(R$10:R10))),"")
If you have Microsoft 365 or Excel 2021, you could use the FILTER function instead. No need to copy the formula down, as it returns all the matches, spilling them below as needed.
=FILTER(Startup!$A:$A,Startup!$B:$B=R$9,"")
byundtThank you so much for this. It's greatly appreciated.
Ah i see, no wonder i could never get match to avoid showing only the first match.
I'm using microsoft 2021 so the filter function works wonderfully for me, but i thank you profusely for providing the aggregate formula as well for me to study it. I knew it was an option when i chanced upon it on a youtube video but I'm not so experienced in excel formulae and i had trouble digesting how to manipulate the aggregate formula within the context of an index. I was only exposed to simple examples of aggregate formulae, not something as complex as this.
Again, thank you so much for this!
