Lookup function

Question

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

Name	Training	Date completed	Name	Training	Status	Date Completed
N1	TR1	function	N1	TR1	Completed	17-12-2021
N2	TR1		N1	TR2	In progress
N3	TR1		N1	TR3	Completed	15-1-2022
N1	TR2		N1	TR4	Completed	6-2-2022
N2	TR2		N2	TR1	Completed	23-10-2021
N3	TR2		N2	TR2	Completed	14-11-2021
N1	TR3		N2	TR3	In progress
N2	TR3		N2	TR4	In progress
N3	TR3		N3	TR1	Completed	17-9-2021
N1	TR4		N3	TR2	In progress
N2	TR4		N3	TR3	In progress
N3	TR4		N3	TR4	Completed	26-10-2021

hansvogelaar · Accepted Answer

Tom_Boonstra&nbsp;
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

Forum Discussion

Lookup function

2 Replies

Resources