Prereq table reverse... VLOOKUP doesn't work

I have a syllabus table that is built manually. Each event in the syllabus has up to 4 prerequisites assigned to it. (This is the left-hand table in the attached file.)

To help me built a flow chart, I want Excel to automatically do the REVERSE of the syllabus chart by generating the right-hand table. As the right-hand table in the attached file shows, I can see which event(s) each event is a prerequisite FOR. It's sort of the converse of the left-hand table.

For example, the left-hand chart shows that TR-C has TR-B and AL-106 as prerequisites. I want Excel to tell me automatically in the right-hand table that AL-106 is a prereq for TR-C and AL-102. With both tables, I can see which prereq each event NEEDS (input by me), and which events are PROVIDED as prereqs (generated by Excel).

I couldn't get VLOOKUP to do this for me, as it only sees the first event that meets the criteria. In this syllabus, an event may be a prereq for more than one event, or sometimes not at all.

Thanks in anticipation!

Good catch!

There are indeed three TR-Cs in the left-hand table, so L12 should be BFM-A.

Perfect!

Thank you very much.

Thanks, Kevin.

That works just as well.