Google sheets key sensitive search bar?
I have duplicated a browser 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 exact word (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&"' ")
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))
- Google Apps Script: Finding an element in a Column and returning the corresponding row
- Find if cell contents exist in array
- Active VS Effective User for Google Sheets
- Rearrange data in table format to rows
- I am not able to extract URL from these cell values using Google Apps Script
- How to count a specific word from a string in a cell from the entire column in google sheets
- Error returned by action script when calling an API with UrlFetchApp.fetch
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- How to execute a Google Apps Script ONLY on the edited row
- How to copy the last row in one table and append to another table in the same GSheet (the same tab)
- Replace duplicate row with new data in Google Sheets using Apps Script
- In Google sheets, how does one either return a blank cell or perform a function based on another cells lack of content?
- Incorrect result of iferror formula via Apps Script
- How to Extract URLs from HYPERLINK in Google Sheets
- Using a reference cell as part of a column in a google sheets query
- Can a formula return corresponding values from one column into another, removing blanks and separating them with commas?
- Google Finance does not show last closing price after market hours. It just shows last *traded* price instead of *closing*
- How to ignore empty rows when submitting to database
- Using Named Functions with New Google Tables
- Google sheets query not returning words with special characters
- How do I compare new IDs with a column of existing IDs and allowcate a timestamp if two IDs match on Google Sheets?
- Adding multiple durations together in google sheets breaking
- Trying to figure out why my GoogleSheets Date formula is not working
- Why is this conditional formatting rule not working in Google Sheets?
- Multiple scripts combined to one script will not run
- Compare two cells and input timestamp in third if they do not match Google Apps Script
- QUERY function with multiple conditions using time frame
- Show next row as soon as a cell is filled, using a macro
- Why does my Google Sheets query simplificaiton result in an error?
- Comparing Dates between 2 Sheets, Not Evaluating as true when conditions are met