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) ))
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))
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 Yundt wow that worked thank you so much!!
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)))
It would be helpful to see your actual workbook and know exactly where you want to put the formula.
I assumed that you are retrieving data from worksheet Walleye JPMcopy) column L, and are looking to return only those values that pertain to EQUITIES. I also assumed that your data start on row 2, and that the formula is also placed on row 2, possibly on a different worksheet. If so, you might try copying down:
=IFERROR(INDEX('Walleye JPMcopy)'!L$2:L$643,AGGREGATE(15,6,(ROW('Walleye JPMcopy)'!D$2:D$643)-ROW('Walleye JPMcopy)'!D$2)+1)/('Walleye JPMcopy)'!D$2:D$643="EQUITIES"),ROWS(D$2:D2))),"")The suggested formula returns an empty string (looks like a blank) when there are no more EQUITIES.
If I guessed incorrectly, please post a sample workbook and tell me exactly which cell needs the formula.
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 CThis 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.
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.
