jeudi 26 janvier 2017

Find all cells that include specific text in another cell, then return those cells in another column of the same row as the specified cell

I need to find a way in Excel to do something like below:

+------+-------+-----------------+ | COL1 | COL2 | Col3 | +------+-------+-----------------+ | A | A_1 | A_1, A_2, A_3 | | C | A_2 | | | B | D_1_A | B_1, B_2, B_2_A | | E | B_1 | | | F | B_2 | | | D | B_2_A | D_1_A | | G | A_3 | | +------+-------+-----------------+

  1. Specify the values in each cell of COL1
  2. In COL2, find all cells that include the values in COL1
  3. In COL3, in a single cell matching the row index of COL1, return all the cell values from COL2 that include the values of the cells in COL1

The wording of the question is confusing but I hope the table makes more sense. I tried numerous IF, SEARCH, INDEX, and VLOOKUP functions but failed to do this correctly. Please note that the list isn't in any particular order (I can't list it alphabetically as the underscores in the cell values mess up other formulas in the workbook). Any help would be appreciated!

Aucun commentaire:

Enregistrer un commentaire