Issue with Index Match

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
282
Office Version
  1. 2016
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)),"")
 
So, what result do you want for twins? How about a small sample with expected results.
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sorry, should have done this in the first place! A separate formula is producing the DOBs in column E (I didn't bother to add this for the purpose here). The result I'm hoping for is the corresponding names for each of the DOBs into column F.

Book3
ABCDEF
1SurnameFirst NameDOBResulting DOBCorresponding Name
2BrownTim3/04/19526/07/1954
3BakerBrian5/06/19567/08/1952
4BiltongKeith6/07/19547/08/1952
5BennettBruce7/08/19526/02/1957
6BennettAndrew7/08/1952
7BundochSimon9/08/1951
8BenningPaul1/02/1953
9BaneKenny3/04/1954
10BaineJohn6/02/1957
11BensenJames7/03/1958
Sheet1
 
Upvote 0
Try: Drag formula down as needed.

Book2
ABCDEF
1SurnameFirst NameDOBResulting DOBCorresponding Name
2BrownTim4/3/19527/6/1954Biltong, Keith
3BakerBrian6/5/19568/7/1952Bennett, Bruce
4BiltongKeith7/6/19548/7/1952Bennett, Andrew
5BennettBruce8/7/19522/6/1957Baine, John
6BennettAndrew8/7/1952
7BundochSimon8/9/1951
8BenningPaul2/1/1953
9BaneKenny4/3/1954
10BaineJohn2/6/1957
11BensenJames3/7/1958
Sheet1
Cell Formulas
RangeFormula
F2:F5F2=IFERROR(INDEX($A$2:$A$11&", "&$B$2:$B$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/($C$2:$C$11=E2),COUNTIF($E$2:E2,E2))),"")
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top