IFERROR, INDEX, MATCH... formula... that also reads dates.

[JasonNeedsHelp]

Hi All,

This one is hard to explain. Please see the attached file.

I need a formula in Column G.

This column reads the "Certificate Database" tab to see if there is a course that it matches with the employee. If so then it returned with the date that the certificate is expired, if there is no match, or state the expired date if in the future.

Here is how I would like the logic to function. I've highlighted the relevant lines.

If Column F is blank = blank.

If Column F Has a value "R" match Column A with Column B in 'Certificate Database' tab.
- Then match Column F1 for match in column D in 'Certificate Database' Tab.
- Return the value of Column E in 'Certificate Database' Tab.

Now if the value is prior to todays date. I would want the text to say "EXPIRED".
Now if there is no match. I would want the text to day "No Record"
Now if there is a match and date is later then today. I want the text to state the expiry date.

Thank you in advance!!

-Jason

Training Database.xlsb

[Jacc]

Something like this then? I converted your first sheet to a Table, that way I don't have to have 10000 row formula. The range expand automatically when data is added.

[stephenloky]

I did a example...see if works for you.

[Speshul]

This can be done with a VLOOKUP instead of index/match. Try this in G3 and copy down.

Please Login or Register  to view this content.

[FDibbins]

Another 1 - an array in sheet2 G3, copied down
...
=IFERROR(IF(F3="","",IF(INDEX('Certificate Database'!$F$2:$F$5801,MATCH(A3&"-"&$F$1,'Certificate Database'!$B$3:$B$9285&"-"&'Certificate Database'!$D$3:$D$9285,0))<TODAY(),"Expired",INDEX('Certificate Database'!$F$2:$F$5801,MATCH(A3&"-"&$F$1,'Certificate Database'!$B$3:$B$9285&"-"&'Certificate Database'!$D$3:$D$9285,0)))),"No Record")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[JasonNeedsHelp]

Something like this then? I converted your first sheet to a Table, that way I don't have to have 10000 row formula. The range expand automatically when data is added.

This seems to work great.

I've never seen a table converted like this. Seems much easier to reference columns. My only concern is that i'm between entry level excel user and not sure what happens when I add columns as this is part of a much larger dataset.

But this does function exactly as I requested! Great!

[JasonNeedsHelp]

I did a example...see if works for you.

Hi Stephen,

This unfortunately didn't work as requested. The output was required, blank or expired. Where instead of required I wanted to see the date of expiry.

Close - just didn't have the right information.

[JasonNeedsHelp]

Another 1 - an array in sheet2 G3, copied down
...
=IFERROR(IF(F3="","",IF(INDEX('Certificate Database'!$F$2:$F$5801,MATCH(A3&"-"&$F$1,'Certificate Database'!$B$3:$B$9285&"-"&'Certificate Database'!$D$3:$D$9285,0))<TODAY(),"Expired",INDEX('Certificate Database'!$F$2:$F$5801,MATCH(A3&"-"&$F$1,'Certificate Database'!$B$3:$B$9285&"-"&'Certificate Database'!$D$3:$D$9285,0)))),"No Record")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

This works great. Much easier to use then the table1 format (because i'm a beginner) as it's just a cut and paste formula.

However, I got some values that didn't' quite add up and I discovered something. There are multiple entries for the same person. The person's certificate would expire and he/she would go and get a recertification. The formula will only pick up the person's earliest certificate. I tried to re-sort by date to see if it would pick up the more recent certificate but didn't have any effect.

I realize this was my oversight and am sorry - but any way to modify this pick up the more newest date and if there are multiple entries?

Thanks!

[JasonNeedsHelp]

This can be done with a VLOOKUP instead of index/match. Try this in G3 and copy down.

Please Login or Register  to view this content.

Hello Speshul,

This worked well for about 70% of the values I tested. I'm not sure what it is with this formula but some of the dates changed.

For example.
AAB00424995 your formula gives me 23-May-16 but the actual is 26-May-16
AAB00424997 your formula gives me EXPIRED but the actual is 4-May-17
AAB00425007 your formula gives me 27Aug-16 but the actual is 20-Aug-16

but the other values would be correct.

Strange.

[Speshul]

VLOOKUP won't pick up duplicates of the lookup value, it will always return the first value it finds on the table. Are there duplicates of those three values?

AAB00424995
AAB00424997
AAB00425007

[FDibbins]

OK this is maybe a better way, it also avoids the use of an array.

Add a helper column to Database (I used Q) and in Q2, copied down (I went to 10 000, use less if you think you wont need that much, just adjust the ranges as needed)...
=IF(COUNTIFS(B2:$B$10000,B2,D2:$D$10000,D2)>1,"",B2&"-"&D2)

Then use this regular formula on Course sheet, copied down...
=IFERROR(IF(F3="","",IF(INDEX('Certificate Database'!$F$2:$F$10000,MATCH(A3&"-"&$F$1,'Certificate Database'!$Q$3:$Q$10000,0))<TODAY(),"Expired",INDEX('Certificate Database'!$F$2:$F$10000,MATCH(A3&"-"&$F$1,'Certificate Database'!$Q$3:$Q$10000,0)))),"No Record")

The helper will pull the last entry for the name/course combo, which I presumed would also be the latest date for the combo

[JasonNeedsHelp]

OK this is maybe a better way, it also avoids the use of an array.

Add a helper column to Database (I used Q) and in Q2, copied down (I went to 10 000, use less if you think you wont need that much, just adjust the ranges as needed)...
=IF(COUNTIFS(B2:$B$10000,B2,D2:$D$10000,D2)>1,"",B2&"-"&D2)

Then use this regular formula on Course sheet, copied down...
=IFERROR(IF(F3="","",IF(INDEX('Certificate Database'!$F$2:$F$10000,MATCH(A3&"-"&$F$1,'Certificate Database'!$Q$3:$Q$10000,0))<TODAY(),"Expired",INDEX('Certificate Database'!$F$2:$F$10000,MATCH(A3&"-"&$F$1,'Certificate Database'!$Q$3:$Q$10000,0)))),"No Record")

The helper will pull the last entry for the name/course combo, which I presumed would also be the latest date for the combo

All of the dates seems to not work. I'm guessing I did something wrong but I've re-read and can't seem to figure out what I did... Attached sheet with your formulas.

[JasonNeedsHelp]

VLOOKUP won't pick up duplicates of the lookup value, it will always return the first value it finds on the table. Are there duplicates of those three values?

AAB00424995
AAB00424997
AAB00425007

Yes, I tried to re-sort as well but I think the more viable option would be a formula that picked the most recent date.

Sorry - this was my mistake as I forgot, only realized after the original post, that there would be multiple entries of the same training for the same person.

[FDibbins]

take a look at the attched

[Jacc]

Thanks for the rep! There are info out there about the Excel Table feature. The advantages makes them well worth learning, if you ask me.
http://www.contextures.com/xlExcelTable01.html

[JasonNeedsHelp]

take a look at the attched

Thanks, I took a look at the sheet you provided and it still wasn't correct. It took me going back and forth a bunch of times to figure out what was happening...

The formula you gave me..

=IFERROR(IF(F3="","",IF(INDEX('Certificate Database'!$F$2:$F$10000,MATCH(A3&"-"&$F$1,'Certificate Database'!$Q$3:$Q$10000,0))<TODAY(),"Expired",INDEX('Certificate Database'!$F$2:$F$10000,MATCH(A3&"-"&$F$1,'Certificate Database'!$Q$3:$Q$10000,0)))),"No Record")

I figured it was offset by one row... as soon I realized that I saw what the issue was... I change your formula to:

=IFERROR(IF(F3="","",IF(INDEX('Certificate Database'!$F$2:$F$10000,MATCH(A3&"-"&$F$1,'Certificate Database'!$Q$2:$Q$10000,0))<TODAY(),"Expired",INDEX('Certificate Database'!$F$2:$F$10000,MATCH(A3&"-"&$F$1,'Certificate Database'!$Q$2::$Q$10000,0)))),"No Record")

and this seemed to work very well.

THANK YOU!!! sorry for all the confusion and all the help. Very much appreciate it!

[JasonNeedsHelp]

Thanks for the rep! There are info out there about the Excel Table feature. The advantages makes them well worth learning, if you ask me.
http://www.contextures.com/xlExcelTable01.html

Thanks for the reference. Will do!