Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination.
-Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times
-Purpose of Device, there are multiple rows with a device (different per row)
Example this is the source:
Row1 X3PM1 NH3
Row2 X3PM1 NF3
I want this to show up in the destination as
Row1 X3PM1 (NH3) (NF3) each (device) in a different cell with a different matching criteria in the formula
I'm trying variations on:=INDEX(COLLECT({Range to Return}, {Range to Search}, CONTAINS("Text String", @cell )), 1)
"=INDEX(COLLECT({Purpose of Device}, {Purpose of Device}, CONTAINS("NH3", @cell )), 1) Seem like the right start, but I need to match the Tool ID.
Or should I use CONTAINS?
=IF(CONTAINS(INDEX({Purpose of Device}, MATCH([Tool ID]@row , {Tool ID Detect})), "NH3")) I just don't know where to put the criteria, NH3 or whatever I want. Straight INDEX/MATCH only returns the first instance of "purpose of device" in the sheet.
"=IF(CONTAINS("NH3", {Purpose of Device}), (INDEX({Purpose of Device}, MATCH([Tool ID]@row , {Tool ID Detect}))))" that isn't doing it either.. It
I know I'm close…
