With the EXACT and MATCH functions, you can match and compare numbers.
This article explains how to use them.
Compare two numbers using EXACT, and the equal sign
The EXACT function takes two strings as parameters and returns TRUE or FALSE.
|
1 |
=EXACT (text1, text2) |
The function works in a similar way as the equal sign, but there are differences. This example illustrates them.
As you can see, almost all the results are the same. The only difference between the EXACT function and comparison with an equal sign is in row 6. Cell B6 is a text string (number with an apostrophe in front). If you compare them with an equal sign, you will get FALSE, but function EXACT returns TRUE.
Different matches with MATCH
The MATCH function works differently than the EXACT function. Instead of returning TRUE or FALSE, it returns the position of a lookup value in a row, column, or table.
|
1 |
=MATCH (lookup_value, lookup_array, [match_type]) |
The MATCH function takes the match type as the third optional parameter. The default value is 0 and it means the exact match.
Example with ascending order
Let’s take a look at how the match types work in practice.
Match types:
1: This parameter looks for the largest value that is smaller or equal to the lookup value. The values in the lookup array have to be placed in ascending order.
0: Returns the exact value. If there is none, it returns the #N/A error. In this case, the value is in the second row of the lookup array (A3).
-1: With this parameter, the values in the lookup array must be displayed in descending order, otherwise this parameter will return an error. This is the case in this example.
Example with descending order
The order of the lookup array was changed. Look, at how it affected the results of the formulas.
Match types:
1: The function with this parameter returns an error because the lookup array is not in ascending order.
0: The number 20 is located in the 5th row of the lookup array (A6).
-1: The lookup array is displayed in descending order, so this time, the formula works. For the number 20, it returns the 5th position (A6) and for 25, it looks for the smallest value, greater than 25, which is 30. 30 is located in the 4th row of the lookup array.