Change date into text if <today and adding text if cell isblank... into an Array.

[JasonNeedsHelp]

Hello All,

I did a post a couple of weeks ago looking for a formula that took information form my datasheet placed it into a table by matching the persons name with the certificate they had and only picking the most recent date... this formula that works great but after looking at the data I need to add two (or this might be three, i'm not sure) new criterias:

Here is the original post:
http://www.excelforum.com/excel-form...est-dates.html

And here is the solution that did everything that i wanted (Shout out to Fortis that solved this):

=IFERROR(IF(MAX(IF('Certificate Database'!$C$2:$C$100=B$5;IF('Certificate Database'!$D$2:$D$100=$A7;'Certificate Database'!$F$2:$F$100)))=0;INDEX('Certificate Database'!$F$2:$F$100;MATCH('Staff Summary - AQ'!B$5&'Staff Summary - AQ'!$A7;'Certificate Database'!$C$2:$C$100&'Certificate Database'!$D$2:$D$100;0));MAX(IF('Certificate Database'!$C$2:$C$100=B$5;IF('Certificate Database'!$D$2:$D$100=$A7;'Certificate Database'!$F$2:$F$100))));"")

Here is a new excel example with the data and the two new criterias highlighted in yellow:

The first new criteria that I have is that I've added hyperlinks to all the certificates that I have which will show up in the LINK column as dates. Anything that I do not have a LINK to is blank and everything that I do is named after the expiration date that I had earlier.

What i would like to see in my results is that everything that does not have a link in column G (Certificate Database) place the date that it is expired along with some text "NC" (to signify No Certificate). I've highlighted the examples of the change on the following sheet...

The second criteria is that if the certificate date is in the past and expired to change the date to just "EXPIRED".

So these are seperate... but this maybe a third criteria that if it's expired and I don't have a certificate it then show "EXPIRED NC". Hope this makes sense...

Thank you all in advance!!!