Excel uses many functions and the MATCH function is also used most commonly. You will find the MATCH function given under Excel LOOKUP and Reference functions. Using the MATCH formula in Excel will make you able to look up a value in an array that helps in returning the value position in the array.
For instance, suppose you need to match the value 5 in the given range A1:A4 that has values 1,5,3,8, the function will return 2, as 5 is considered the second item in the range. The MATCH function in combination with other functions is used in financial analysis.
Matching Formula in Excel – Syntax, and Uses
In a cell range, the MATCH function finds out for a specific value that returns the relative position of the value.
The syntax would be as:
MATCH(lookup_value, lookup_array, [match_type])
Lookup_value: In this argument, the value is added that is supposed to find. The value could be an integer, text, logical value, or a cell reference as well.
Lookup_array: This one is also required and it adds the range of cells to search.
Match_type: This argument is optional that shows the match type. This argument includes values such as 1, 0, and -1.
1 or omitted: This value is added by default which helps in finding the largest value used in the lookup array. It helps in sorting the lookup array in ascending order from smallest to largest.
0: It finds the first value given in the array, which is exactly equal to the lookup value. For this, you don’t need sorting.
-1: It helps in finding the smallest value in the array greater than or equal to the lookup value. It needs to be sorted in descending order from largest to smallest.
For a better understanding of the MATCH formula in Excel, let’s use the formula with data. In the dataset, we have students’ names given in column A and the exam marks in column B that are aligned from largest to smallest. We will find the level of a student by using the simple formula:
=MATCH(“Laura”, A2:A8, 0)
Or else you can add the lookup value in a cell and refer to it in the Excel MATCH formula:
=MATCH(E1, A2:A8, 0)
You can see that student names are added in an arbitrary order and that’s why we set the match_type argument to 0. It happens because this match type is no longer needed to sort values in the lookup array.
Exact Match
In the table given below, you can see the serial number (S.N.), name, and department of ten employees. In this dataset, the employee “Tanuj” position needs to find out:
The following formula can be applied:
“=MATCH(F4,$B$4:$B$13,0)”
In this formula, the “match_type” is set at 0 which helps in returning the exact position of the “Tanuj” employee (lookup_value) from the given range $B$4:$B$13 (lookup_array). The output is 1.
Approximate Match
In the given list below you can see the values from 100 to 1000. We want to find the approximate position of the value 525.
The following formula is used:
“=MATCH(E19,B19:B28,1)”
In this formula, you will see the “match_type” sets at 1 to return the approximate match of 525 (lookup_value) from the given range B19:B28(lookup_array). For the largest value (500), the MATCH function looks for less than 525 in the given array and the outcome is 5.
Wildcard Match
With wildcards, the MATCH function is able to perform a MATCH function where the match type is set to zero. The below example is a clear view of this case and the formula in E3 is:
The MATCH functions return the outcome of wildcards as “pq.”
Things to Remember
- Match returns the #N/A error when there is no match is found.
- A MATCH function is not case-sensitive.
- The argument lookup_array has to be in descending order: True, False, Z – A,…9,8,7,6,5,4,3,…and so on.
- In the Lookup_value argument, the wildcard characters such as an asterisk or a question mark are used when the match_type appears to be 0 and lookup_value appears to be text.
- The wildcard characters including an asterisk and a question mark will appear in lookup_value when match_type is equal to 0 and lookup_value is in the text format.
Things to Consider about the MATCH Function
The matching formula in Excel is easy to use, thus a few things you must be aware of are:
- With MATCH function helps in returning the relative position of the lookup value given in the array and not the value itself.
- The MATCH formula in Excel is case-sensitive, which clearly shows that it is not able to differentiate lowercase and uppercase letters while dealing with text values.
- The #N/A error returns when the lookup values are not found in the lookup array.
- When the lookup array has many values of the lookup, you will see the position returns of the first value.
