INDEX MATCH obtaining values for duplicate names

Question

I have one example table with the following data in Sheet1 with the following random data

------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

Then I have another table in Sheet2

------A ----------------- B ----------------------C ------------------------D  
    1 --First--------------Last-----------------Start Date--------------End Date  
    2 --John--------------Smith---------------------------------------------------  
    3---John--------------Smith---------------------------------------------------  
    4---John--------------Smith---------------------------------------------------  
    5---Abel--------------Jones---------------------------------------------------  
    6---Abel--------------Jones---------------------------------------------------

I am using INDEX MATCH to transfer the data between the two sheets.

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

To populate column C with the start dates from Sheet1.

=INDEX(Sheet1!$D:$D,
    MATCH(1,INDEX((Sheet1!$A:$A=$A3)*(Sheet1!$B:$B=$B3),0),0))

and this to populate column D with the end dates.

The problem is, when I perform this INDEX MATCH function, for a duplicate name, it will only copy over the first value. So this formula will paste 08/08/2014 into all 'John Smith' Start dates in column C of Sheet2.

How do I obtain all values so that C2 should be 08/08/2014, C3 should be 08/11/2014, C4 should be 06/06/2014 etc.

Brendan Brendan · Accepted Answer · 2015-06-19T14:44:35

One solution would be to insert a column in both sheets with a "running count" of instances of the same name. For example, insert col C and in C2 enter =IF(A2&B2 = A1&B1, C1+1, 1). This starts the count at 1 if the concatenated first and last name is new, and increases the previous count by 1 if not. So you would have

First     Last         Count by Person
John      Smith        1
John      Smith        2
John      Smith        3
Abel      Jones        1
Abel      Jones        2
George    Washington   1
Thomas    Jefferson    1
Thomas    Jefferson    2

You can then add this column to your MATCH() function (and change the lookup column as necessary).

Edit: It is worth noting that this requires your raw data is sorted by name.

INDEX MATCH obtaining values for duplicate names

2 Answers