Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

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

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?

Solution

  • 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)))

    0