After a long time, here is yet another Excel Vs Sheets comparison tutorial. The EXACT function slightly differs in its use in Excel and Google Sheets. So this time the post is about the function EXACT and its difference in usage as well as output in two popular Spreadsheet applications.
If you use the EXACT function for its ‘correct’ purpose, then you won’t find any difference in the use of this function in Excel and Google Sheets. What is that ‘correct’ purpose?
The purpose of EXACT in both Excel and Google Sheets is for comparing two strings or you can say two texts. If the compared strings match, then the function would return the Boolean value TRUE, else FALSE.
The Boolean output of this function will differ when you use numeric values as the arguments in both Excel and Sheets.
In addition to this, the Array use of the EXACT function is different in these applications and, I think, that goes without saying. Read on for more details.
EXACT in Excel Vs EXACT in Google Sheets – Matches
Here are the syntaxes of EXACT in Excel and Google Sheets.
Syntax: Excel.
exact(text1,text2)Syntax: Google Sheets.
exact(string1,string2)Though the syntax of the EXACT function seems different in both Excel and Sheets, actually both are the same. ‘Text’ and ‘String’ actually refer to the same.
From the argument itself, you can understand that the EXACT is a text function in Excel and Google Sheets.
Here is one example of the use of this function in Google Sheets.
The formula in cell C1 tests whether the string in cell A1 (Apple) and B1 (APPLE) are exactly the same. Since both are different from the point of view of ‘case-sensitivity’, the formula returns FALSE.
Please go through the other two formulas and you can understand how it works in Google Sheets. It works similarly in Excel too.
In string comparison using EXACT, the case-sensitivity of the strings (texts) matters. So in some case-insensitive functions, to bring case-sensitivity, you can use this function.
Example:
=if(exact("Apple","apple")=FALSE,"Doesn't Match","Matches Perfectly!")EXACT Function in Excel and Google Sheets – Differences
Now let’s see where the EXACT function differs in Excel and Google Sheets.
EXACT ArrayFormula
To apply a single formula for a range, we can make use of the ARRAYFORMULA function in Google Sheets and Curly Braces (Ctrl+Shift+Enter) in Excel.
You May Like: Array Formula: How It Differs in Google Sheets and Excel.
So the said difference applicable to the EXACT array formula use in these two Spreadsheet applications.
In the just above example, in Google Sheets, instead of the formula in cell C1, C2, and C3, we can use a single EXACT array formula in cell C1.
=ArrayFormula(exact(A1:A3,B1:B3))But in Excel, the case is not the same. Here is the step to use an EXACT array formula in Excel.
- Select the range C1:C3.
- Type the formula
=EXACT(A1:A3,B1:B3). - Hit Ctrl+Shift+Enter.
The above is one example of how the EXACT function differs in Excel and Google Sheets. But it’s not about the difference in output but in usage.
Also please do understand that this array formula difference is there when you compare any other functions in these two popular Spreadsheet applications.
Numeric Values as Arguments
Here are the most surprising differences between EXACT in Excel and Google Sheets.
When you use the EXACT function in EXCEL the number formatting doesn’t affect the comparison. But in Google Sheets, it does matter.
For example, $10 and 10 will be two different values (FALSE) in Google Sheets but will be the same (TRUE) in Excel.
The same is the case with decimal places, thousand separators, and percentage formatting. You can easily understand the same with the below example screenshot taken from Google Sheets.
See the highlighted FALSE Boolean values. The same formulas in Excel will return TRUE in all the cases. Quite surprising, right?
To match the Excel behavior, you must use the function N with the EXACT as below in a non-array form in Google Sheets.
Enter the below formula in cell D2 and drag down.
=exact(n(A2),n(B2))Alternatively, you can use the following EXACT array formula in Google Sheets.
=ArrayFormula(exact(n(A2:A6),n(B2:B6)))Actually we can make use this peculiarity of the EXACT function in Google Sheets in conditional formatting.
I mean we can highlight formatted or unformatted numbers in a sheet based on our choice.
At present, I didn’t explore this possibility. I’ll research more on this and may come back with another conditional formatting related tutorial later.
That’s all. Thanks for the stay, enjoy!
Resources (Case Sensitivity)
- How to Do a Case Sensitive Sumproduct in Google Sheets.
- Case Sensitive Reverse Vlookup Using Index Match in Google Sheets.
- How to Perform a Case Sensitive COUNTIF in Google Sheets.
- How to Do a Case Sensitive SUMIF in Google Sheets.
- Case Sensitive Vlookup in Google Sheets [Solved].
- How to Do a Case Sensitive DSUM in Google Sheets.
