Mar 25 2022 04:13 AM
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 |
Mar 25 2022 04:27 AM
SolutionUse =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
Mar 25 2022 04:27 AM
SolutionUse =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