Share via

Index Match 2 sets of data to to get latest date value from a range of date values for the same variable

Anonymous
2024-09-27T07:43:05+00:00

I am trying to call out the latest dates in Column D for this same person (there is a whole long list of other names and NRIC numbers below) but unable to do so. 

When I use =INDEX(Sheet A! D:D, MATCH(H2,SheetA!B:B,0)) = I will only get the top most date which is D2 (the effective date of 20-09-2017. But I want the latest date for this Bugs Bunny person which is 20-09-2024. How do I do this? 

Sheet A

A B C D
1 Name NRIC Citizenship EffectiveDate
2 Bugs Bunny Bo S1234567F Singaporean 20-09-2017
3 Bugs Bunny Bo S1234567F Singaporean 20-09-2017
4 Bugs Bunny Bo S1234567F Singaporean 20-09-2017
5 Bugs Bunny Bo S1234567F Singaporean 20-09-2017
6 Bugs Bunny Bo S1234567F Singaporean 13-05-2018
7 Bugs Bunny Bo S1234567F Singaporean 20-09-2024

Sheet B

G H
1 Name NRIC
2 Bugs Bunny Bo S1234567F
3 Bugs Bunny Bo S1234567F
4 Bugs Bunny Bo S1234567F
5 Bugs Bunny Bo S1234567F
6 Bugs Bunny Bo S1234567F
7 Bugs Bunny Bo S1234567F

There are 2 sheets because I need to reference Sheet B to Sheet A and pull up the data from Sheet A to verify the data of Sheet B. Hope this clarifies. 

Thanks heaps to anyone who can solve this..

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2024-09-28T04:43:08+00:00

    You are welcome. My formula should still work. I cannot guess the mistake that you are committing.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-09-28T04:27:12+00:00

    This one?

    =MAX(FILTER(SheetA!D:D,SheetA!B:B=H2))

    Image

    Thanks Snow! Really appreciate your help looking into my problem. I could replicate your solution. but when I tried it maybe because I have too much data. 1117 rows of different names with corresponding NRICs I got a "0" value. My excel is Microsoft 365 Apps for Enterprise. Is there an alternative formula to cope with large data sets?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-09-28T04:13:08+00:00

    Hi,

    In cell H2, enter this formula and drag down

    =MAXIFS($D$2:$D$7,$A$2:$A$7,F2,$B$2:$B$7,G2)

    Alternatively, you may enter this formula in cell H2 (it will spill down automatically)

    =MAXIFS($D$2:$D$7,$A$2:$A$7,F2:F7,$B$2:$B$7,G2:G7)

    Hope this helps.

    Image

    Hi Asish,

    Thanks so much for responding to my post. Yes almost there. the formula below works well for me but only for small data sets like this example here.

    In cell H2, enter this formula and drag down

    =MAXIFS($D$2:$D$7,$A$2:$A$7,F2,$B$2:$B$7,G2)

    I replicated the excel spreadsheet and I find it works just by NRIC alone. So column B and corresponding column G - as names tend to get tricky I realise when some names not in correct type case i.e. Proper and Upper case it complicates things. The NRIC are unique enough identifiers.

    But I have 1117 rows of data with different names with corresponding NRICs to reference, and the excel couldn't do it as too much data and only return a "0". Anyway to filter that much of data (see below as example) your formula does well for small data sets even with different names below but if there are 200 other different names, excel can't run the formula and comes back with a "0". Any other alternative? Again, appreciate all the help here.

    A B C D E F G H
    1 Name NRIC Citizenship EffectiveDate Name NRIC EffectiveDate
    2 Bugs Bunny Bo S1234567F Singaporean 20/9/2017 Bugs Bunny Bo S1234567F 20/9/2024
    3 Bugs Bunny Bo S1234567F Singaporean 20/9/2017 Bugs Bunny Bo S1234567F 20/9/2024
    4 Bugs Bunny Bo S1234567F Singaporean 20/9/2017 Bugs Bunny Bo S1234567F 20/9/2024
    5 Bugs Bunny Bo S1234567F Singaporean 20/9/2017 Bugs Bunny Bo S1234567F 20/9/2024
    6 Bugs Bunny Bo S1234567F Singaporean 13/5/2018 Bugs Bunny Bo S1234567F 20/9/2024
    7 Bugs Bunny Bo S1234567F Singaporean 20/9/2024 Bugs Bunny Bo S1234567F 20/9/2024
    8 James Jack S2345678G Singaporean 20/9/2017 James Jack S2345678G 4/10/2024
    9 James Jack S2345678G Singaporean 20/9/2017 James Jack S2345678G 4/10/2024
    10 James Jack S2345678G Singaporean 20/9/2017 James Jack S2345678G 4/10/2024
    11 James Jack S2345678G Singaporean 4/10/2024 James Jack S2345678G 4/10/2024

    Was this answer helpful?

    0 comments
  4. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2024-09-27T23:41:10+00:00

    Hi,

    In cell H2, enter this formula and drag down

    =MAXIFS($D$2:$D$7,$A$2:$A$7,F2,$B$2:$B$7,G2)

    Alternatively, you may enter this formula in cell H2 (it will spill down automatically)

    =MAXIFS($D$2:$D$7,$A$2:$A$7,F2:F7,$B$2:$B$7,G2:G7)

    Hope this helps.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2024-09-27T09:13:41+00:00

    This one?

    =MAX(FILTER(SheetA!D:D,SheetA!B:B=H2))

    Was this answer helpful?

    0 comments