Buzz
Excel functions play a crucial role in simplifying complex tasks, helping users conveniently tackle their work. The Match function in Excel serves as an important example. In this article, Mytour will guide you through its purpose and the simplest usage of the Match function.
1. What is the Match function in Excel?
In Excel, there are numerous functions to make operations more convenient. Among them, the Match function is used to search for the position of data within a data range. After the search, the function precisely reveals the exact result regarding the position of that value within the data range.
The Match function is employed to find the position of data within a specified range.
2. Syntax of the Match function in Excel
The syntax of the Match function is constructed as follows: =Match(Lookup_value;Lookup_array;Match_type).
In this context:
- Lookup_value (search value): The value you want to look up in the search array. This is a mandatory value for the function to execute successfully.
- Lookup_array (search array): Array or range of cells to search. This is also a mandatory value for the lookup to take place.
- Match_type (matching type): The type of search/match. This value is optional and can be omitted. It includes:
Number -1
Returns an error because the values in the range B2:B5 are not sorted in descending order.
Number 0 (Exact Match): Search for the value exactly equal to the lookup_value. If using this search type, you can freely arrange the values in Lookup_array in any order. Example:
The position of the value 41 in the range B2:B5.
Number 1 or omit (Less than): Search for the largest value less than or equal to the lookup_value. If using this search type, the values in lookup_array must be sorted in ascending order. Example:
Due to no exact match, it will return the position of the nearest lower value (38).
3. Types of MATCH functions in Excel
Exact Match
This function delivers precise results for the search value. It operates when Match_type is set to 0.
The function returns an exact result, which is 4.
Approximate Match
This function provides results equal to the largest value less than or equal to the search value. It executes when Match_type is set to 1.
The function yields an approximate match result of 7.
Wildcard Match
This function provides results matching the wildcard character. It executes when Match_type is set to 0.
The function will return a result of 6 - matching the position of the wildcard character.
Using Match function with wildcard characters:
Wildcard Characters in Match Function
- Question mark (?): Represents any single character.
- Asterisk (*): Represents any number of characters. Note that this character is only used in the Match function when the third parameter of the function is set to 0.
To better grasp the concept, let's explore an example: Finding the relative position of names starting with 'Car' in an Excel table as shown.
Using the Match function with wildcard (*) will yield a result of 2:
=MATCH(E1&'*';A2:A11;0)
Applying the Match function with wildcard (*) will result in 2.
Using the Match function with wildcard (?) will yield a result of 5 - the relative position of the name 'Baker':
=MATCH('ba?er';A2:A11;0)
Applying the Match function with wildcard (?) will result in 5.
5. Utilizing Match function with case sensitivity
To use the Match function with case sensitivity, we need the assistance of the EXACT function, resulting in the formula:
=MATCH(TRUE; EXACT (search range; search value);0)
The EXACT function compares the search value with each value in the search range. If both compared and comparing cells match 100%, the function returns TRUE; otherwise, it returns FALSE.
After EXACT completes, the Match function uses the TRUE values returned by EXACT for further search. At this point, we can effectively use Match with case sensitivity.
Utilize Match and Exact functions to implement the formula as shown in the figure.
DON'T MISS OUT on some outstanding computer mouse models at Mytour:Above are some instructions on using the Match function in Excel that we want to introduce. Hope it will be helpful to you in your work.
