Multiple Supersessions to One Description

Question

Hello&nbsp;&nbsp;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)&nbsp;Thanks in advance.&nbsp;Sal

sergeibaklan · Answer

Salim2906&nbsp;
If A3:F9 is name as Range, formula here

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

sergeibaklan · Answer

TheAntony&nbsp;
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))&gt;0)*ROW($A$3:$A$8),
               MMULT(--ISNUMBER(SEARCH(H3,$B$3:$F$8)),SEQUENCE(COLUMNS($B$2:$F$2),,,0))
)),"no such"))
&nbsp;

salim2906 · Answer

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

sergeibaklan · Answer

Salim2906&nbsp;
You are welcome, glad to help

theantony · Answer

SergeiBaklan&nbsp;and&nbsp;Salim2906&nbsp;&nbsp; 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)))
            )
         )
      )
   )&nbsp;

Forum Discussion

Multiple Supersessions to One Description

5 Replies

Resources