Lookup function

New Contributor

Hi. The function I am looking for is a Lookup function, but I do not get it right. I have given two simplified tables below to explain. I need a function for the Date completed in table 1. Who can help me? Thanks in advance. Regards, Tom.


Look up in table 2 the same name and training as in table 1. Check if the status is "Completed". If yes, return the 'Date Completed' in this cell. If no, return the value "0" in this cell.


Table 1   Table 2   
NameTrainingDate completed NameTrainingStatusDate Completed
N1TR1function N1TR1Completed17-12-2021
N2TR1  N1TR2In progress 
N3TR1  N1TR3Completed15-1-2022
N1TR2  N1TR4Completed6-2-2022
N2TR2  N2TR1Completed23-10-2021
N3TR2  N2TR2Completed14-11-2021
N1TR3  N2TR3In progress 
N2TR3  N2TR4In progress 
N3TR3  N3TR1Completed17-9-2021
N1TR4  N3TR2In progress 
N2TR4  N3TR3In progress 
N3TR4  N3TR4Completed26-10-2021
2 Replies
best response confirmed by Sergei Baklan (MVP)


Use =INDEX($H$4:$H$15,MATCH(1,($E$4:$E$15=A4)*($F$4:$F$15=B4),0)) and set the number format to the custom format dd-mm-yyyy;;0

Sample workbook attached

@Hans Vogelaar, Thanks!!