Forum Discussion

Salim2906's avatar
Salim2906
Copper Contributor
Jul 05, 2020

Multiple Supersessions to One Description

Hello    Help needed in writing a formula that will give me the Description (column A) if I type any variation of Part # and its Supersessions (column B-F)   Thanks in advance.   Sal
Formulas and Functions
TheAntony's avatar
TheAntony
Iron Contributor
Jul 06, 2020

SergeiBaklan and Salim2906 

  Purely out of curiosity to see if it can be done, I took a shot at a formula to find any text that's part of the table and return a list of outcomes.

=LET(sResult,SEARCH(H3,$B$3:$F$8)/SEARCH(H3,$B$3:$F$8)*SEQUENCE(COUNTA($A$3:$A$8)),
      TRANSPOSE(
         INDEX($A$3:$A$8,
            UNIQUE(
               AGGREGATE(15,6,sResult,SEQUENCE(COUNT(sResult)))
            )
         )
      )
   )

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 06, 2020

TheAntony 

If so I'd avoid LET() since it's on Beta channel only

=TRANSPOSE(IFERROR(INDEX(A:A,
     FILTER(--(MMULT(--ISNUMBER(SEARCH(H3,$B$3:$F$8)),SEQUENCE(COLUMNS($B$2:$F$2),,,0))>0)*ROW($A$3:$A$8),
               MMULT(--ISNUMBER(SEARCH(H3,$B$3:$F$8)),SEQUENCE(COLUMNS($B$2:$F$2),,,0))
)),"no such"))

 

Resources