Multiple Supersessions to One Description

Copper Contributor

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)

 

Salim2906_0-1593967732988.png


Thanks in advance.

 

Sal

5 Replies

@Salim2906 

If A3:F9 is name as Range, formula here

image.png

could be

=IFERROR(INDEX(Range,INDEX(SUMPRODUCT((Range=H4)*ROW(Range))-ROW(Range)+1,1),1),"no such")

@Sergei Baklan Thank you so much for the quick response. This seems to solve my problem.  

@Salim2906 

You are welcome, glad to help

@Sergei Baklan 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)))
            )
         )
      )
   )

 

@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"))