如何将Google Sheets中用于程序检索的QUERY公式修改为大小写不敏感

Hey there! I get it—dealing with case-sensitive searches can be frustrating when you just want to find a program regardless of how it’s capitalized. Let’s fix that formula of yours to make it case-insensitive.

The Core Idea #

Google Sheets’ QUERY function is case-sensitive by default for text matches. To get around this, we’ll convert both the column values and your search inputs to the same case (lowercase, in this example) before comparing them. That way, "ProgramA" and "programa" will be treated as identical.

Modified Formula #

Here’s your updated formula with case-insensitive checks for all text fields:

=QUERY(Programs!A3:V, "where "&TEXTJOIN(" "&A2&" ", 1, 
  IF(B2<>"", " LOWER(B)='"&LOWER(B2)&"'", ), 
  IF(C2<>"", " LOWER(C)='"&LOWER(C2)&"'", ), 
  IF(D2<>"", " LOWER(A)='"&LOWER(D2)&"'", ), 
  IF(E2<>"", " LOWER(E)='"&LOWER(E2)&"'", ), 
  IF(F2<>"", " F="&F2&"", ), 
  IF(G2<>"", " LOWER(G)='"&LOWER(G2)&"'", ), 
  IF(H2<>"", " LOWER(H)='"&LOWER(H2)&"'", ), 
  IF(I2<>"", " LOWER(I)='"&LOWER(I2)&"'", ), 
  IF(O2<>"", " LOWER(O)='"&LOWER(O2)&"'", ), 
  IF(P2<>"", " LOWER(P)='"&LOWER(P2)&"'", )
))

What Changed? #

For every text-based search condition (all except F2, which looks like a number), we made two key adjustments:

• Wrapped the column reference in LOWER() (e.g., LOWER(B) instead of just B), which converts all values in that column to lowercase.
• Wrapped your search input in LOWER() (e.g., LOWER(B2) instead of B2), which converts whatever you type into that cell to lowercase.

Since we’re comparing lowercase to lowercase, the case of the original data or your search input no longer matters. The number field (F2) stays the same because numbers don’t have case sensitivity to worry about.

Just paste this formula into your sheet, and your search should now work seamlessly regardless of capitalization!

内容的提问来源于stack exchange，提问作者Tod Poirier