I have two tables:
The first is a table of roles where each person is in a team but person c has changed teams in June:

The second is a table of signings where it has the name of who signed it and when it was signed. What I need is a dynamic array formula that will show the team name of that person at the time of signing so signing 5 would be when person c was in team x, but signing 6 was when c was in team y. Person d doesn’t exist in the role table so would need to show “not found”