Jul 05 2020 09:49 AM
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
Jul 05 2020 11:06 AM
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")
Jul 05 2020 11:53 AM
@Sergei Baklan Thank you so much for the quick response. This seems to solve my problem.
Jul 05 2020 12:29 PM
You are welcome, glad to help
Jul 05 2020 08:33 PM
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)))
)
)
)
)
Jul 06 2020 07:38 AM
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"))