Excels INDEX MATCH - Finding multiple matches

Question

------A ----------------- B ----------------------C ------------------------D  
1 --First--------------Last-----------------Start Date--------------End Date  
2 --John--------------Smith--------------08/08/2014------------01/01/2015  
3---John--------------Smith--------------08/11/2014------------17/11/2014  
4---John--------------Smith--------------06/06/2014------------23/12/2014  
5---Abel--------------Jones--------------14/05/2014------------29/04/2015  
6---Abel--------------Jones--------------04/07/2014------------26/04/2015

Sometimes on my spread sheet I get duplicate names. For example the table above (with random data) there are 3 John's and 2 Abel's. With these names is a start and end date. If I have a start or end date which is earlier or later than the previous entry for that name, I would calculate the longest range (John Smith) by doing

=MAX($D2:$D4)-MIN($C2:$C4)

This would give 209 days.
Also, I want the formula to automatically recognize if a name has a duplicate within the range, and if so retrieve the date. In order to do this, I perform an INDEX MATCH function as follows.

    =INDEX(C:C,
MATCH(1,INDEX((A:A=$A3)*(B:B=$B3),0),0))

This should give 08/08/2014.
So then I combine the two formulas together to perform a search for first name and last name comparing the start and end date of the matches, to find the longest possible date range.

=MAX(
   (INDEX($C:$C,
       MATCH(1,INDEX(($A:$A=$A4)*($B:$B=$B4),0),0)))
:$D4)
-
MIN(
   (INDEX($D:$D,
       MATCH(1,INDEX(($A:$A=$A4)*($B:$B=$B4),0),0)))
:$C4)

Again this gives 209.
The problem which I am now having with this formula, is that when the INDEX MATCH function scans through the list of names to find a duplicate, if it does, it will only match against the first. If there is more than one duplicate, it will ignore it. There are 3 John Smiths in the table, I want the formula to check for the longest range between all duplicates. Does this make sense and is this possible?

Jerry Beaucaire Jerry Beaucaire · Accepted Answer · 2015-06-10T13:52:53

I would define the ranges more surgically when using array formulas. Given the small data set, this array formula gives the expected result:

=MAX(IF(INDEX($A$2:$A$6&" "&$B$2:$B$6, 0) = A2&" "&B2, $D$2:$D$6)) - MIN(IF(INDEX($A$2:$A$6&" "&$B$2:$B$6, 0) = A2&" "&B2, $C$2:$C$6))

...confirmed with Control+Shift+Enter to activate the array.

Excels INDEX MATCH - Finding multiple matches

1 Answers