Help creating a formula that using If,index, match and displays only the newest dates...

Hello All,

I'm trying to create a visual spreadsheet of training courses and their expiration date for each person. Please see the following link for reference:

https://docs.google.com/spreadsheet/...Gc&hl=en#gid=0

On the first sheet "Certificate Database" i manually enter all the information in this sheet for the training done. What i would like to see on the second sheet "Staff Summary - AQ" is that it would search the datasheet employee column for the employee's name (on the header) and then search if there is a matching course. Where I get stuck is if true for course and name match on multiple entries that it picks the newest recent date (or "no exp" text).

What i mean by newest date = in my example Jane Doe has done trailering twice on February 15, 1999
and on April 16, 2011 but on my summary sheet it only shows the most current April 16, 2011.

I've tried index and match to get matches but when it is False I get N/A and always get the date that matched first and not the newest date. Because my datasheet will constantly be changing filtered and sorted by others I only want the summary to show me the newest date. Also if possible, prefer not to do an array formulas as the ctrl+shift+enter breaks when i keep adding new entries and is awefully slow. This is a small example of the sheet i'm working with but the original takes forever to load (I have about 50 courses and 1000 employees which equates to about 12,000 entries currently).

Can anyone help with a simple index,match formula that shows only the most current date (or if "no exp" text is present).

Thank you greatly in advance!!!

Hi and welcome to the forum

Please upload your sample workbook here, not all members can access file-hosting sites like google docs (company firewalls and stuff)

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Hi

As you have mixed text and dates in the same column, formula is a little complicated but does what you need. It's an ARRAY formula.

Formula:

Please Login or Register  to view this content.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

The problem is exactly this that you described in your first post!

..I've tried index and match to get matches but when it is False I get N/A and always get the date that matched first and not the newest date

That's why we need the ARRAY formula--MAX(IF.....