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
Salim2906's avatar
Salim2906
Copper Contributor
Jul 05, 2020

SergeiBaklan Thank you so much for the quick response. This seems to solve my problem.  

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 05, 2020

Salim2906 

You are welcome, glad to help

  • 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