Google sheets query search box to search a value in different columns

user_19833400 0
Viewed 20

good night/day.

I've been having issues to search a value no matter the column, for example If I need a specific value from col1, col3 or col4, I would only need to type the value I need in Cell B1 no matter if it is lowercase or uppercase , But can't find the way to solve it.

=QUERY(IMPORTRANGE(Links!C2,"Supply!A2:L"),
"SELECT Col1, Col2, Col3, Col4, Col6, Col7, Col11, Col10, Col9, Col8, Col12
 WHERE lower('Col1&Col3&Col4') CONTAINS '"&LOWER(B1)&"'",0)

Instead It drops me #N/A (Error Query completed with an empty output) when searching for a specific value in Cell B1.

Thanks and have a good one, whoever can help me out!

google-query-language google-sheets-formula match import arrays
1 Answers

try:

=ARRAYFORMULA(QUERY({IMPORTRANGE(Links!C2,"Supply!A2:L"), 
 IMPORTRANGE(Links!C2,"Supply!A2:A")&
 IMPORTRANGE(Links!C2,"Supply!C2:C")&
 IMPORTRANGE(Links!C2,"Supply!D2:D")},
 "select Col1,Col2,Col3,Col4,Col6,Col7,Col11,Col10,Col9,Col8,Col12
  where lower(Col13) contains '"&LOWER(B1)&"'", 0))
user_56326290
Related