Forum Discussion
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 sheet I would like it to return a list of all the names matching these two criteria. My formula is as follows:
=INDEX('Horse data'!B2:B100, MATCH(1, ($I$13='Horse data'!C2:C100) * ($K$13='Horse data'!D2:D100), 0))
The problem I have with it is that it returns duplicates, because for 2 or 3 cells, the first best match is the same, until my search area has moved down enough to continue on to the next match.
Is there any way to rewrite this formula to return a list of 'unique distinct values' and avoid duplicate returns?
Preferably without adding more columns, but if there's no other way I'd accept any solution!
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 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 Yes, that works great! Thanks a lot!
certavi , you are welcome
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
Hi!
SergeiBaklanBrad Yundt
I'm working with an index match formula for an array of results. The formula I now use:
{=IF(ISERROR(INDEX($I$4:$L$48;SMALL(IF(COUNTIF($G$4:$G$15;$I$4:$L$48);MATCH(ROW($I$4:$L$48);ROW($I$4:$L$48));"");ROWS($B$4:B4));COLUMNS($I$3:I3)))=TRUE;"";INDEX($I$4:$L$48;SMALL(IF(COUNTIF($G$4:$G$15;$I$4:$L$48);MATCH(ROW($I$4:$L$48);ROW($I$4:$L$48));"");ROWS($B$4:B4));COLUMNS($I$3:I3)))}
The problem is the results of the formula in the array return the same values (from the 'name' column) if the 'main status' and 'second status' rows are filled.
I need the result of 1 name only even though the 2 rows of status are filled. Is there any formula to do that? I used Excel 2013.
Thank you! I Already post in here https://techcommunity.microsoft.com/t5/excel/index-match-avoid-duplicate-returns/m-p/3568176=IFERROR(INDEX($I$4:$L$48,AGGREGATE(15,6,(ROW($K$4:$L$48)-ROW($K$4)+1)/((COUNTIF($G$4:$G$15,$K$4:$K$48)+COUNTIF($G$4:$G$15,$L$4:$L$48))>0),ROWS(B$3:B3)),COLUMNS($B3:B3)),"")Brad
Brad_Yundt you are awesome! You've helped many people here 🙂 I hope that you can help me as well 🙂
Problem: duplicated values ... as always 🙂 for Index with ...
My Case:
I must prepare the list of winners for competition with two conditions:Number of points - max
Driving time - lowest (if competitors gathered the same number of points)
Dataset looks like:The fromulas for:
B26: =UPPER(INDEX($B$4:$B$20,MATCH(LARGE(IF($C$4:$C$20=$B$24,$N$4:$N$20),$A26), IF($C$4:$C$20=$B$24,$N$4:$N$20),0)))
C26: =UPPER(INDEX($A$4:$A$20,MATCH(LARGE(IF($C$4:$C$20=$B$24,$N$4:$N$20),$A26), IF($C$4:$C$20=$B$24,$N$4:$N$20),0)))
D26: =LARGE(IF($C$4:$C$20=$B$24,$N$4:$N$20),$A26)
I know that there is missing something to add addtional condition to "Time". Never the mind, I've received duplicates 😞
Can you help me with fixing this formula - choice the best driver based on score and if two or more drivers receive the same score counting by "time" wtihout duplicates?
Many thanks in advance!
- Hi Everyone,
I have a similar problem with extracting data from different excel spreadsheets and getting duplicate values. I’m utilizing this formula but was looking for a way on how to remove all duplicate values the formula is returning
=SUMIF(‘Cattle’!B:B,Comparison!A2,’Cattle’!Q:Q)
Please help me as soon as possible- Formulas don't remove values. But perhaps I'm not understanding your real need.
Do you want to exclude duplicate entries on your Comparison worksheet from reporting the sum twice?
Do you want to return the corresponding value for only the first match from Cattle column B?- Exclude duplicate entries on my comparison worksheet from reporting the sum twice . So i’m utilizing uniqiue identifiers which is A2 on Comparison sheet, but there are mutiple unique identifiers on column A. I want to remove those duplicates without interfering with the SUMIF formula
