Need help with applying a formula to this scenario

[punksterz626]

Hello Gurus,

I really need help with formulating a formula for this scenario. I want to index and match column "Job #" from TABLE 1 to TABLE 2 and return the Status Date; however, the tricky part is the Job # may have multiple dates. In order to capture the correct date, I also want to match the first two letters of "Job Status" column from TABLE 1 to TABLE 2 and return the correct status date on TABLE 2.

EX. The first job # on the list is 277518 and has the Job status AB, the result i want would be TABLE 2 AB_Date 3/23/2023.

I have attached the spreadsheet as an example.

Thank you for helping.