• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match Searching for the Most Recent Date

J

JuliusV

Member
I have attached a spreadsheet with dummy data.

Essentially, I am trying to find a way to index match the Volunteer Tab's Column to identify the date last paired for the Volunteers name by looking at the Date Paired Column in the Matchings Tab.

Currently, one volunteer can be matched with multiple mentees, but I have to manually identify the latest date. I am hoping there is a manual way to perform this by looking at the date paired section.

Thanks for the help!
 

Attachments

  • Finding the Most Recent Date with Index Match.xlsx
    15 KB · Views: 1
For example:
Jane Doe has 4 mentees with one on 4/20/14, 4/13/15, 4/15/15, and 4/27/15.
When doing the index match on the date matched, I want the most recent date to show. Therefore, in this case it would be 4/27/15.
 
Hi:

Please find the attached The formula colored in orange,

This is a CSE formula , Execute it by pressing Control+Shift+Enter keys.

Thanks
 

Attachments

  • Finding the Most Recent Date with Index Match.xlsx
    16.8 KB · Views: 7
I believe this formula is only pulling the first instance of a date.
For example, the last name 1 first name 1, should be 4/14 not 4/8.
The dates are not ordered and I think that's the difficulty
 
Hi:

The dates are pulling correctly if you see the name combination of
"Last Name1First Name1", 14/4 is the latest date available not 8/4.
(see the 8th row of your Matchings tab).

Thanks
 
Ahh I see, my mistake. I was looking at the wrong column. :/


Thank you so much for the help! I really appreciate it.
 
Is there a way to use Index Match with Max to identify the most recent date; rather than using an array which requires CSE?

I also didnt realize that if you made the formula into array form that it acts the same as an index match.

Thanks for the additional help.
 
Hi:
I am not sure this is possible coz you have to consider the entire array of the date and match it with the unique ID. BTW what the issue with the array formula?

Thanks
 
There's no issue with the array formula, I Kurt prefer there to be no arrays to make it easier when passing the workbook for someone else to maintain.
 
Hi:

If you do not need a formula the next option is to pull a pivot from the data.


Thanks
 

Attachments

  • Finding the Most Recent Date with Index Match.xlsx
    21.1 KB · Views: 1
Last edited:
You must log in or register to reply here.
Back
Top