Filtering by case-sensitive string equality

Question

I discovered (when answering this question) that string comparison in filter is case insensitive: the formulas =filter(A:A, B:B = "Yes") and =filter(A:A, B:B = "YES") have the same output.

Apparently, this is true generally for string comparison in Google Sheets: ="Y"="y" returns TRUE, and so does its equivalent =eq("Y", "y").

This is convenient sometimes, but what to do when I want to filter rows by case-sensitive string equality?

Answer 1

Short answer

Use REGEXMATCH(text, regular_expression) instead of = or EQ(value1,value2) to set the conditions for FILTER(range,condition1,[condition2, ...])

Demostration

Data source

+---+---------+---------+
|   |    A    |    B    |
+---+---------+---------+
| 1 | Field 1 | Field 2 |
| 2 | Yes     | A       |
| 3 | YES     | B       |
| 4 | no      | C       |
| 5 | No      | D       |
| 6 | NO      | E       |
| 7 | yes     | F       |
+---+---------+---------+

Formula in D1

=FILTER(A2:B7,REGEXMATCH(A2:A7,"^yes$"))

(The word is placed between ^ and $, so that the entire cell content is required to match it.)

Result

+---+-----+---+
|   |  D  | E |
+---+-----+---+
| 1 | yes | F |
+---+-----+---+

Answer 2

Short answer

Instead of FILTER use QUERY

Demostration

Data source

+---+---------+---------+
|   |    A    |    B    |
+---+---------+---------+
| 1 | Field 1 | Field 2 |
| 2 | Yes     | A       |
| 3 | YES     | B       |
| 4 | no      | C       |
| 5 | No      | D       |
| 6 | NO      | E       |
| 7 | yes     | F       |
+---+---------+---------+

Formula in D1

=QUERY(A1:B7,"select B where A = 'yes'",1)

Result

+---+---------+
|   |    D    |
+---+---------+
| 1 | Field 2 |
| 2 | F       |
+---+---------+