SOLVED

New Contributor

# Lookup function

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
2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: Lookup function

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

# Re: Lookup function

@Hans Vogelaar, Thanks!!