New Contributor

# 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)

Sal

5 Replies

# Re: Multiple Supersessions to One Description

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

# Re: Multiple Supersessions to One Description

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

# Re: Multiple Supersessions to One Description

You are welcome, glad to help

# Re: Multiple Supersessions to One Description

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)))
)
)
)
)``````

# Re: Multiple Supersessions to One Description

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