Google sheets key sensitive search bar?-CodePudding

I have duplicated a tab and I have implemented a search bar to filter only the row for the word I am looking for... however if I don't write the word exactly (lower or upper case) nothing will show up...

the formula I am using is =QUERY(IMPORTRANGE("1xxxxx8RviacxxxxxOWXx_xxxxrEi-xxxxDi_xx","Roster!B3:I"),"SELECT * WHERE Col1 CONTAINS '"&G1&"' or Col2 CONTAINS '"&G1&"' or Col3 CONTAINS '"&G1&"' or Col4 CONTAINS '"&G1&"' or Col5 CONTAINS '"&G1&"' or Col6 CONTAINS '"&G1&"' or Col7 CONTAINS '"&G1&"' or Col8 CONTAINS '"&G1&"' ")

Any help will be very much appreciated.

CodePudding user response：

you could try wrapping the matching columns/cell within lower to avoid this mismatch.

=ARRAYFORMULA(QUERY({to_text(IMPORTRANGE("1xxxxx8RviacxxxxxOWXx_xxxxrEi-xxxxDi_xx","Roster!B3:I"))},"SELECT * WHERE lower(Col1) CONTAINS '"&lower(G1)&"' or lower(Col2) CONTAINS '"&lower(G1)&"' or lower(Col3) CONTAINS '"&lower(G1)&"' or lower(Col4) CONTAINS '"&lower(G1)&"' or lower(Col5) CONTAINS '"&lower(G1)&"' or lower(Col6) CONTAINS '"&lower(G1)&"' or lower(Col7) CONTAINS '"&lower(G1)&"' or lower(Col8) CONTAINS '"&lower(G1)&"' "))

Alternate Formula:

=filter({B3:I},REGEXMATCH(BYROW(B3:I,LAMBDA(bix,TEXTJOIN("|",1,bix))),"(?i)"&G1))

CodePudding user response：

try:

=LAMBDA(x, FILTER(x, REGEXMATCH(FLATEN(QUERY(TRANSPOSE(x),,9^9)), "\b"&G1&"\b")))
 (IMPORTRANGE("1xxxxx8RviacxxxxxOWXx_xxxxrEi-xxxxDi_xx", "Roster!B3:I"))

or insensitive:

=LAMBDA(x, FILTER(x, REGEXMATCH(FLATEN(QUERY(TRANSPOSE(x),,9^9)), "(?i)\b"&G1&"\b")))
 (IMPORTRANGE("1xxxxx8RviacxxxxxOWXx_xxxxrEi-xxxxDi_xx", "Roster!B3:I"))