=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?
3 Answers
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)&"'" 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.
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
