How to Use the Google Sheets EXACT Function to Compare Text Strings

When working with large datasets in Google Sheets, you frequently need to verify if two pieces of text are identical. While you can use the standard equals operator (e.g., =A2=B2) for basic comparisons, this method has a significant limitation: it completely ignores case sensitivity.

If you need to verify passwords, user IDs, product SKUs, or secure access codes where capitalization strictly matters, the standard equals operator will fail you. This is where the EXACT function becomes essential.

The EXACT function compares two strings of text and returns a simple TRUE or FALSE result. Crucially, it only returns TRUE if the text strings are completely identical, right down to the capitalization and hidden spaces.

Understanding the EXACT Syntax

The EXACT function is incredibly simple to implement. The syntax requires exactly two arguments:

=EXACT(string1, string2)

• string1: The first text string or cell reference you want to compare.
• string2: The second text string or cell reference to compare against the first.

The function evaluates both strings. If they match perfectly, the cell will output TRUE. If there is even a single difference—whether it is a capital letter, a missing punctuation mark, or an extra space—it will output FALSE.

Practical Examples of Using EXACT

1. Verifying Case-Sensitive Data

Suppose you are auditing a list of product codes. In your inventory system, “apple” and “Apple” are two completely different items. Using =A2=B2 would incorrectly tell you they are the same.

Instead, use the EXACT function:

• If cell A2 contains “SKU-100a” and cell B2 contains “SKU-100A”.
• The formula =EXACT(A2, B2) will return FALSE because the ‘a’ is lowercase in the first string and uppercase in the second.

2. Finding Hidden Spaces

Data imported from other software, copied from websites, or manually entered by users often contains hidden leading or trailing spaces. These invisible characters can break VLOOKUPs and MATCH functions.

Visually, “John Doe” and “John Doe ” (with an extra space at the end) look identical in a spreadsheet.

If you suspect hidden spaces are causing errors, you can test the data using EXACT.

• If A2 is “Data” and B2 is “Data “.
• =EXACT(A2, B2) will return FALSE, instantly alerting you to the presence of hidden formatting characters.

Combining EXACT with Other Functions

While EXACT is useful on its own, its real power emerges when nested inside other logical functions.

Creating Case-Sensitive IF Statements

You can use EXACT as the logical test within an IF statement to trigger specific outcomes based on a case-sensitive match.

For example, if you want to output “Match Found” only when the text matches perfectly, and “Discrepancy” if it does not:

=IF(EXACT(A2, B2), "Match Found", "Discrepancy")

Building Case-Sensitive Lookups

By default, VLOOKUP and XLOOKUP in Google Sheets are not case-sensitive. If you look up “apple”, they might return the data for “APPLE”.

You can force a case-sensitive lookup by combining the FILTER function with EXACT.

Suppose your lookup values are in Column A, your return data is in Column B, and the specific case-sensitive term you want to find is in cell D2.

Use this formula:

=FILTER(B:B, EXACT(A:A, D2))

This creates a filtered array that only pulls data from Column B where the corresponding value in Column A is a perfect, case-sensitive match for the value in D2.

Handling Numbers and Formatting

It is important to understand how EXACT handles numbers and cell formatting. EXACT evaluates the underlying value of the cell, not necessarily how it appears visually.

• If cell A2 contains the number 100 (formatted as plain text) and cell B2 contains the number 100 (formatted as a number), =EXACT(A2, B2) will return TRUE.
• However, if A2 contains 10.50 and B2 contains 10.5, EXACT will return FALSE, even if you have formatted B2 to visually display two decimal places.

By using the EXACT function, you ensure strict data integrity and prevent subtle formatting or capitalization errors from undermining your Google Sheets analysis.