The vast majority of Microsoft Excel functions do not distinguish between uppercase and lowercase letters, or are sometimes said to be case insensitive. In most cases, this is just good, because it is difficult to predict in advance exactly how the word we need will be written in the initial data: “Moscow”, “MOSCOW” or “Moscow”.
However, there are situations where this case insensitivity turns out to be out of topic and you need to check-look for an absolutely exact match, taking into account uppercase and lowercase letters. Then the function can come to the rescue EXACT (EXACT).
It has a very simple syntax:
=EXACT(Meaning1; Meaning2)
It checks if both values are the same (case sensitive!) and produces a boolean output THE TRUTH (TRUE) or LYING (FALSE). Notice how, in the example below, the result of EXACT differs from a simple comparison that does not distinguish between uppercase and lowercase letters:
Let’s look at a couple of scenarios for using the function EXACT (EXACT) on practice.
For example, using a simple formula, you can quickly calculate the number of exact matches with a given value in a certain list or the sum of exact matches:
In this case, the function EXACT compares each element of the list in turn with the given value and returns TRUE or FALSE. The double minus sign turns TRUE into 1 and FALSE into 0. To add the resulting ones and find the total number of exact matches, use the function SUMPRODUCT (SUMPRODUCT). It also allows you to enter our formula without the obligatory combination Ctrl + Shift + Enter, although it is, in fact, an array formula.
If it is necessary to implement search and extraction of data by exact match, i.e., a kind of VPR (VLOOKUP) case-sensitive, you will have to shaman a little harder, but this is also possible.
- Case sensitive search and substitution
- Selective counting with functions SUMMIF(-MN) and its analogues
- Use cases for the function SUMPRODUCT (SUMPRODUCT)