Excel has several functions to check for string matches.
There are two types of functions to check for exact matches and partial matches.
These functions are mainly used with the judgment function, the conditional aggregation function, and the search function.
This section introduces the characteristics of each function and the appropriate situations for each.
Exact match
Comparison Operator
TRUE if all strings match.
The method is simply to create a formula in which the strings enclosed in " (double-cotation marks) are inserted between each other using =.
=Text1 = Text2
This is an example of the use of the IF function.
TRUE only if there is no difference.
Even if it contains the object to be checked, as in the third line, it is considered to be different, and the result is FALSE.
This comparison operators is easy to use, but it is not case-sensitive, so use the EXACT function if you want to make strict comparisons.
EXACT Function
The difference between the comparison operators and the comparison operator is as follows, so use whichever is more convenient at the time.
In most cases, the EXACT function is more advantageous.
| Case | Difference |
|---|---|
| "Character numbers" and "Numeric values". | = : If the data type is different, it is judged as different. EXACT function : If the numbers are the same, it is judged to be the same. |
| Upper and lower case alphabetical characters. | = : Not case sensitive. EXACT function : Case sensitive. |
The EXACT function is used like this
=EXACT(Text1,Text2)
partial match
Partial match, first match, or backward match, A special character called a wildcard is used to create formulas.
It is mainly used for conditional tally functions, XLOOKUP functions, and VLOOKUP functions.
It cannot be used as is for IF functions (see below).
These wildcards include the following.
| - | Description |
|---|---|
| * | Zero or more characters of text. |
| ? | 1 character. |
| ~ | Treat * and ? as normal characters. |
Examples of use in conditional tally and VLOOKUP functions
? is used to clearly specify the number of characters.
Conversely, if you do not want to specify the number of characters, use *.
This allows you to perform fuzzy searches with VLOOKUP functions and conditional tally functions.
In the XLOOKUP function, the Match_mode must be changed.
| Comparison method | Formula Example |
|---|---|
| Partial match | *Text* |
| First match | Text* |
| Backward match | *Text |
Example Usage in IF Functions
Wildcards are not allowed in the IF function. But it can be made to work by inserting a COUNTIF function in between.
=IF(COUNTIF(search target, text), "Hit", "Miss")
In the case of the FILTER function
The FILTER function cannot use wildcards, so it must be handled by a string functions.
---