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) ))
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
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.
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.
This is what I see after clicking Open full text editor. Note the "cloud" icon at the very bottom for attaching files.
byundtAs seen from my photo, there wasn't such a button. But i've realized that i could have just directly dropped the file into the text editor and it'll attach the file.
Thank you anyway. Both formula works wonderfully!
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!
byundtHello, apologies but where is the paperclip icon? It's not visible on my end, so i don't see where i can attach files. I've looked through every single icon, and none indicates an option to attach files.
Attach file icon is not available for all users, have no idea why so. Alternatively you may share the file on OneDrive, GoogleDrive or so.
