Using index and match to look up employee ID, skill code and return relevant exp date

[McHutch]

Hi everyone

I am attempting to create a skill matrix for a work group. I have a clean data source whereby I can match an employee ID and skill code to return the relevant expiry date. From there I will apply traffic light conditional formatting to highlight training that needs to be renewed.

I am having trouble creating an index / match formula that can handle my two criteria. I have attached a sample data set. Given the large number of employees, it is not practical to use helper columns.

Appreciate any help offered to solve!

[Beamernsw]

Hi McHutch, try pasting the following formula into Cell B3 and then copy down and across.
Then you can set up your conditional formatting as per the dates you want. e.g. Red = Expired, Green = Current, Orange = 3 months left.

Please Login or Register  to view this content.

[McHutch]

That works! Thank you so much
Any advice on how to overcome the 00/00/1900 error where a result doesnt exist? I usually use IFERROR in front of the formula but this doesnt seem to be working

edited: Found a solution by using custom format of dd/mm/yyyy;;

Thanks again for the help

[McHutch]

My solution to remove the 0/01/1900 error does work as far as cell display however when applying conditional formatting to my file, cells with this value are still being captured as a date in the past. Any thoughts on how to remove this error altogether? I have been able to handle #NA error with IFERROR(" ") add in to my formula but the 1900 date error still appears. Updated file attached for reference

Advice appreciated

[AliGW]

For example in E3:

=IFERROR(1/(1/INDEX(DATA!$A:$I,MATCH(1,INDEX(($A3=DATA!$A:$A)*(E$1=DATA!$E:$E),0,1),0),9)),"")

[McHutch]

Thank you so much AliGW. That solved my problem. I have a work friend that will be so impressed with me when I show her this tomorrow! I'll be sure to share the credit

[AliGW]

Thanks.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.