The EXACT function has slight differences in its usage between Excel and Google Sheets. In this post, we will explore the EXACT function and highlight the differences in both usage and output across these two popular spreadsheet applications.

When you use the EXACT function for its intended purpose, you typically won’t notice any difference in how it operates in Excel and Google Sheets. What is that intended purpose?

The EXACT function is designed to compare two strings (or texts). If the compared strings match exactly, the function returns the Boolean value TRUE; otherwise, it returns FALSE.

However, the Boolean output of this function can vary when you use numeric values as arguments in Excel versus Google Sheets.

Additionally, the array usage of the EXACT function differs between these applications. Let’s delve into the details.

EXACT in Excel vs. EXACT in Google Sheets – Syntax

Here are the syntaxes for the EXACT function in both Excel and Google Sheets:

Syntax in Excel:

EXACT(text1, text2)

Syntax in Google Sheets:

EXACT(string1, string2)

Though the syntax appears slightly different in both applications, text and string refer to the same concept. From the argument names, it’s clear that EXACT is a text function in both Excel and Google Sheets.

EXACT Function in Excel and Google Sheets – Comparison of Matches

In Google Sheets, you can use the EXACT function as follows:

=EXACT(A1, B1)

If cell A1 contains “Apple” and cell B1 contains “APPLE,” the formula will return FALSE because the comparison is case-sensitive.

Here are two more examples to illustrate how it works in Google Sheets. The function operates similarly in Excel.

Case Sensitivity in EXACT Function

The EXACT function is case-sensitive, making it useful for enforcing case sensitivity in functions that are otherwise case-insensitive. For example:

=IF(EXACT(A1, "AR 123"), 500, 0)

In contrast, the following formula is not case-sensitive:

=IF(A1="AR 123", 500, 0)

Differences in EXACT Function Between Excel and Google Sheets

Now let’s explore where the EXACT function differs in Excel and Google Sheets.

EXACT with Array Formulas

To apply a single formula across a range of cells, you can use the ARRAYFORMULA function in Google Sheets, while Excel traditionally uses array entry (Ctrl+Shift+Enter) for array functions in versions that do not support dynamic arrays. However, in versions of Excel that support dynamic arrays (such as Excel 365), there is no need to use Ctrl+Shift+Enter; you can simply press Enter.

This difference is evident in how you use the EXACT function in an array formula:

In Google Sheets, you can use a single formula to exactly match values in A1:A3 with the corresponding values in B1:B3:

=ARRAYFORMULA(EXACT(A1:A3, B1:B3))

To achieve the same result in older versions of Excel, follow these steps:

1. Select the range C1:C3.
2. Type the formula:
   =EXACT(A1:A3, B1:B3)
3. Press Ctrl+Shift+Enter.

In Excel 365 and later versions, you can simply enter:

=EXACT(A1:A3, B1:B3)

This demonstrates a difference in usage rather than output. It’s important to note that this distinction in array formula handling applies to many other functions in these two spreadsheet applications.

Numeric Values as Arguments

One of the most notable differences between EXACT in Excel and Google Sheets involves how numeric values are treated.

In Excel, the formatting of numbers does not affect comparisons; thus, $10 and 10 are considered equal (returning TRUE). In contrast, Google Sheets treats them as different values, returning FALSE. The same applies to decimal places, thousand separators, and percentage formatting.

For example, consider the following comparisons:

• Google Sheets: $10 vs. 10 → FALSE
• Excel: $10 vs. 10 → TRUE

To achieve Excel-like behavior in Google Sheets, you can use the N function with EXACT as follows:

Assuming the values to compare are in A2:A6 and B2:B6, enter the following formula in cell D2:

=EXACT(N(A2), N(B2))

Drag down to apply the formula to other cells.

Alternatively, you can use the ARRAYFORMULA to check a range:

=ARRAYFORMULA(EXACT(N(A2:A6), N(B2:B6)))

This peculiarity of the EXACT function can be leveraged in conditional formatting. You can highlight formatted or unformatted numbers in a sheet based on your preferences.

I haven’t fully explored this possibility yet, but I plan to research more on this topic and may return with a conditional formatting-related tutorial in the future.

Thank you for reading! Enjoy your day!

Resources

• Using Exact Match Criteria in Google Sheets Database Functions
• Exact Match Using Regular Expression in Google Sheets