Issue with Index Match

[Jasesair]

I have the below formula which is returning a name (Column B) after matching a Date of Birth in Column C. The index-match formula is copied down a large table. It all works perfectly until there are twins with the same DOB. Any idea how to resolve?

=IFERROR(INDEX($B$6:$B$203,MATCH(AT6,$C$6:$C$203,0)),"")

 

[mrshl9898]

On option

=TEXTJOIN(",",TRUE,IF(C6:C203=AT6,B6:B203,""))

 

[Jasesair]

I'm guessing my version of Excel doesn't offer TEXTJOIN.

 

[mrshl9898]

Should be there in 2016. Is that the correct version?

 

[Jasesair]

Office Professional Plus 2016. Definitely not recognising TEXTJOIN.

 

[mrshl9898]

Another option (dragged down)

=IFERROR(INDEX($B$6:$B$203, SMALL(IF(($AT$6=$C$6:$C$203), MATCH(ROW($C$6:$C$203), ROW($C$6:$C$203)), ""),ROWS($A$1:A1))),"")

 

[mrshl9898]

And a bit relating to missing TEXTJOIN, you may need to update by the sounds of it.

Redirecting

answers.microsoft.com

 

[Jasesair]

I might need a complete rethink because your formula isn't returning anything at all unfortunately.

 

[AhoyNC]

See if this works. I assume as you drag your formula down AT6 goes to AT7 etc.

=IFERROR(INDEX($B$6:$B$203,AGGREGATE(15,6,(ROW($B$6:$B$203)-ROW($B$6)+1)/(AT6=$C$6:$C203),COUNTIF($C$6:C6,C6))),"")

 

[Jasesair]

That works perfectly. It just doesn't address the original issue of a duplicate (twins with the same DOB).