CODEWITHSUNDEEP
functiongoogle-sheets
Andrew Anderson
Andrew Anderson

Reputation: 1126

Making QUERY function case insensitive

=QUERY(D4:F385;"select D,F where D contains '"&J4&"'")

If in J4 cell we have tree, this query grabs cells containing tree, but not Tree.

How to make it case insensitive?

Upvotes: 23

Views: 36520

Answers (3)

Emil Jan
Emil Jan

Reputation: 41

The following worked for me when I tested it with lower case, upper case, camel case and a mixture of lower and upper cases:

=QUERY(D4:F385;"select D,F where LOWER(D) contains LOWER("""&J4&""") ")

I tested it with the following values in cell J4: Tree tree TREE TrEe TRee TrEe

Upvotes: 4

Luca
Luca

Reputation: 311

The answer is unfortunately not complete, because now it will only work with "tree", but not with "Tree" or with "TREE". The correct solution would be to render always lower also the reference, as in:

=QUERY(D4:F385;"select D,F where LOWER(D) contains '"&lower(J4)&"'"

Upvotes: 31

Andrew Anderson
Andrew Anderson

Reputation: 1126

Changing the formula from: =QUERY(D4:F385;"select D,F where D contains '"&J4&"'") to: =QUERY(D4:F385;"select D,F where LOWER(D) contains '"&J4&"'") will do the trick!

The more elegant solution would be welcomed.

Upvotes: 7

Related Questions