Excel MATCH Function
The Microsoft Excel MATCH function searches for a specific value in a range of cells, and returns the relative position of this value.
Return valueThe MATCH function will return a number representing the position of a value you are searching for.
1. The MATCH function is not case-sensitive.
2. The Match function will return the #N/A error value when failing to find a match.
3. The MATCH function allows to use the wildcard characters in the lookup_value argument for approximate match.
Example 1: MATCH function for exact match
Please do as follows to return the positon of Charles Monaghan in range B3:B7.
Select a blank cell and enter the below formula into it and press the Enter key to get the result.
Note: In the formula, D4 contains the look up value; B3:B7 is the range of cells that contains the value you are searching for; number 0 means that you are looking for the exact match value.
Example 2: MATCH function for approximate match
This section is talking about using the MATCH function to do the approximate match searching in Excel.
As the below screenshot shown, you want to look for the position of number 2500 in range B3:B7, but there is no 2500 in the list, here I will show you how to return the position of the largest value that is less than 2500 in the list.
Select a blank cell, enter the below formula into it and then press the Enter key to get the result.
Then it will return the position of number 2000, which is the largest value that is less than 2500 in the list.
Note: In this case, all values in the list B3:B7 must be sorted in ascending order, otherwise, it will return #N/A error.
For returning the position of the smallest value (says 3000) that is greater than 2500 in the list, please apply this formula:
Note: All values in the list B3:B7 must be sorted in descending order in case of returning #N/A error.
Example 3: MATCH function for wildcard match in MATCH function
Besides, the MATCH function can perform a match using wildcards when the match type is set to zero.
As the below screenshot shown, for getting the position of the value which begins with “hi”, please do as follows.
Select a blank cell, enter the below formula (you can replace the D4 directly with "hi*") into it and press the Enter key. It will return the position of the first match value.
Tip: The MATCH function is not case-sensitive.