Google Sheets, get column names if Col1 match Col2 or Col3

Question

I got a problem and not sure how to solve it.

It's related to Column B in "SR Sheet", in this Google Sheet:

https://docs.google.com/spreadsheets/d/1U13dvWPk8-hMybmhE1RKZ66y7RXXGPWt91_BNFWIup4/edit?usp=sharing

(can edit)

I got 2 main sheets "SR" and "FormdataFilter".

SR in the main sheet. FormdataFilter is where the data comes from.

SR Sheet:

• Col A: contain the name of an item.
• Col B: contain a list of names related to the item, seperated by comma. I have input the names manually to show what i'm trying to do.

FormatdataFilter Sheet:

Each player can pick two items (#1 and #2)

• Col B: Name
• Col C: Item #1
• Col D: Item #2

SR Sheet contain a list of items and the players that picked that item. A player can pick the same item twice.

Any suggestions how to handle this?

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, {FormdataFilter!G2:G, 
 IF(FormdataFilter!H2:H="", FormdataFilter!I2:I, FormdataFilter!H2:H&
 IF(FormdataFilter!I2:I="",,", ")&FormdataFilter!I2:I)}, 2, 0)))