Forum Discussion
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...
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) ))
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!
Looks like you need a tie-breaking provision. Typically, I add (or subtract) the row number divided by a large number to break ties. In your case, I suggest subtracting the time divided by a large number to break the ties..
=UPPER(INDEX($B$4:$B$20,MATCH(LARGE(IF($C$4:$C$20=$B$24,$N$4:$N$20)-$O$4:$O$20/4096,$A26), IF($C$4:$C$20=$B$24,$N$4:$N$20-$O$4:$O$20/4096),0)))
=LARGE(IF($C$4:$C$20=$B$24,$N$4:$N$20),$A26)
If you have Microsoft 365, you can use SORT, SORTBY and FILTER functions. Better yet, you don't even need to copy the formulas down. They spill their results automatically.
=UPPER(SORTBY(FILTER($B$4:$B$20,$C$4:$C$20=$B$24,""),FILTER($N$4:$N$20-$O$4:$O$20/4096,$C$4:$C$20=$B$24,""),-1))
=SORT(FILTER($N$4:$N$20,($C$4:$C$20=$B$24)*$N$4:$N$20,""),,-1)
