Query or filter based on ANY column in the source sheet matching a given cell

Question

I'd like a formula to return all the matching values from column A if ANY of columns B-AZ equal the query value. Said differently:

=query(DATA!A:Z, "select A Where 'DATA!B:AZ' = C2").

Formulas I cobbled together, but don't work:

=query('Inv by shelf'!A:AZ,"Select A WHERE '"&C1&"' = '"&TEXTJOIN("|",1,'Inv by shelf'!$B:$AZ)&"'",1)

=filter('Inv by shelf'!A2:A,'Inv by shelf'!B:AZ = C1)

TIA!

Answer 1

Try FILTER() formula with MMULT().

=FILTER('Inv by shelf'!A2:A,MMULT(ArrayFormula(--('Inv by shelf'!B2:Z=C1&"")),SEQUENCE(COLUMNS('Inv by shelf'!B2:Z2),1,1,0)))

Functions references.

MMULT
SEQUENCE
FILTER

See you workbook sheet harun24hr.