Excel lookup and populate

Excel lookup and populate

Good morning all, I have a requirement to do a lookupand populate a tab
within a worksheet accordingly. I have a spreadsheet that has multiple
tabs that depict different areas in various applications. And one tab that
has a list of requirements. Within the various application tabs, against
each line item I have the requirement it is mapped to. What I am looking
to do is in the requirement tab, have one column that has list of all the
applications that are mapped to this requirement. An example is as below -
the last table depicts what the resultant output should look like: As you
can see a particular screen id in each of the application tabs could
satisfy more than one reqID and hence the last table should capture this
in the "Mapped Application Screen IDs" column. Alternatively, I do not
know if it will be easier to use the ReqID spreadsheet as a basis and if I
find the occurrance in any of the other tabs then I get the screenID. Its
just a thought as I am confused on how to do that
Application A tab:
Screen ID Req ID Screen Name Comment
123 1 Test 1
456 1 Test 2
Application B tab:
Screen ID Req ID Screen Name Comment
789 1 Test 3
ABC 2,3 Test 4
Application C tab:
Screen ID Req ID Screen Name Comment
DEF 2 Test 5
HIJ 1,3 Test 6
Requirements Tab: (desired result)
Requirement ID Requirement Name Mapped Application Screen IDs Comment
1 Req1 123,456,789,HIJ
2 Req2 ABC,DEF
3 Req2 ABC,HIJ