I'd like some help formatting this (Tbl-A)(Loaded via Power Query)

into this (Tbl-B)

What I used to populate Tbl-B Columns (All the same Index/Match formula logic):

Employee Id - IFERROR(INDEX($B$2:$B$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")

Time In - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")

Time Out - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$2, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")

Problems:

- If someone fails to clock in or out, the entire column is thrown off
- The In and out times are not linked to the Employee ID so they are essentially listed in order of appearance. The red on Tbl-B indicates the start of error (Matching Tbl-A)

Please let me know the best way of going about this whether in Power Query or excel.

Many thanks for any help with this