MATCH function only needs two mandatory parameters and an optional parameter
- The researched value
- Reference data (a range of cell) with necessarily 1 dimension (a single row or a single column)
- [Optional] Exact value or an approaching value (possible values 0, 1 or -1 and 1 is the default value)
Examples in column
In the following document, you have in column A the day of the week. With the selection list in cell B1 you will choose one of the days of the week, the MATCH function in C2 will return position of the B1 value in the list of column A.
You can see the formula by double-clicking in the cell C2.
If there is no match, the function returns #N/A
The 0 is needed in this example because days do not follow the alphabetical order. So to make sure the function returns the position of a value in your list, add the optionnal value 0 or FALSE
In this new example, formula does not change but order of values in column A has been changed. You can see now that is you write Monday in c1, the MATCH function returns 5 because Monday is the fifth element of our list.
Examples in row
There is no difference between the fact that the data is written in a row or column. In this example, we will study impact on the third parameter with lists sorted in ascending and descending, and unsorted list.
You can change values in the rows 5, 15 and 25 in the following document to see impact on the result.
- parameter 0 (exact match) always returns the position when the value exists (whether series is sorted or not)
- parameters -1 work only on sorted data in descending order. You can use it on series such as decreasing interest rates or duration of a loan.
Most generally, we work with increasing series without missing value. So you will use mostly the value 0 which is not the default parameter (default value is 1 TAKE CARE)
Use of the function with VLOOKUP
MATCH function is not really useful for itself. But, you can associate it with VLOOKUP and INDEX functions to build the research parameters of these functions.
In the following example, we have the quotation of LVMH and we have build a tableau to return last 10 quotation (with the function LARGE). Because both tables have the same structure, we can replace third parameter of the VLOOKUP function by the result of the function MATCH. Function MATCH will return position of words First, High, … in the first row and the function VLOOKUP expect exactly this value.
If the formula seems to difficult, you can analyze each part of it with shortcut F9.
You can see an example of use of MATCH in the INDEX function too.