Find values from a table and transfer matches to another table

[JoeC-105]

I need some help comparing 2 tables and pulling out the data I need. 

Currently we have a table of cost codes and all the users who are allowed to use them, I want to switch that and have a list of users with the cost codes they are able to use. 

Table 1 example (Source)

Cost Code	Approved Users
645A	worthington; badekar; king; fleming;
138D	woods; fleming;
230A	withers; badekar;
150A	wise;

Table 2 example (what it should like)

User	Cost Codes
worthington	645A
badekar	645A; 230A
king	645A
fleming	645A; 138D
woods	138D
withers	230A
wise	150A

I've tried a variety of formulas but I keep getting #ERROR, #VALUE, #NAME, and #NA errors. Any help would be appreciated as I have approx. 540 cost codes and I'd rather not do them each individually..

[#2]

@JoeC-105 That would be something for Power Query (PQ). You familiar with that?

Attached file contains a PQ solution. Come back here if you need more help.

[#3]

@JoeC-105 

Alternative Power Query option attached

[#4 · Accepted solution]

The comment that I used and that worked just fine was deleted for whatever reason so I'll put it here. 

---

@OliverScheurich said

=TEXTJOIN("; ",,FILTER($A$3:$A$6,ISNUMBER(SEARCH(A10,$B$3:$B$6))))

This formula finds and combines the cost codes for each user. The formula is filled down from cell C10 in the example.

=BYROW(A10:A16,LAMBDA(x,TEXTJOIN("; ",,FILTER(A3:A6,ISNUMBER(SEARCH(x,B3:B6))))))

With Office 365 or Excel for the web you can apply this formula which spills the results. This formula is in cell D10 in the example.

---

I used the first formula and it would perfectly, saved me a lot of time, thank you.