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!
EDIT:
https://docs.google.com/spreadsheets/d/1Vx6ZM59eY8k7YB-QXc0QHPlZR1BAu9Mi4KkeVmgb3bk/edit?usp=sharing
Location by SKU should return all the locations('inv by shelf'A:A) that contain the value of C1
CodePudding user response：
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.
See you workbook sheet harun24hr.