The MATCH Function in excel is used to locate the lookup value in a row, column or table. It is usually combined with the INDEX function to retrieve a particular value based on a row lookup or column lookup which the MATCH Function calculates. The MATCH function is able to check approximate and exact matches, and even partial matches through wildcards (*?). In this post, we would walk you through every aspect of the MATCH function and provide examples of its use in modeling. Let’s begin with the syntax and argument.
Syntax & Argument
The Syntax for the MATCH Function in excel is as follows:
=MATCH(lookup_value, lookup_array, [match_type])
There are 3 arguments in total for the MATCH Index, 2 are required and 1 is optional:
lookup_value: This is the value to match in the lookup_array.
lookup_array: Range of cells or array that excel checks for the lookup_value.
match_type (optional): Used to specify the type of match. Exact or next smallest match (denoted by 1) is the default match for the function. 0 is for exact match only and -1 is for exact or next largest match.
In the next section we would review usage notes for the MATCH Function before we take on some examples. Let’s go…
Usage Notes
These are the things to note when using the MATCH Function:
- The MATCH function returns the position of the lookup_value within the lookup_array and not the value itself.
- If the lookup_value isn’t found in the lookup_array, the #N/A error is returned.
- If there are many occurrences of the lookup value, the function returns the position of the first occurrence.
- The MATCH Function is case insensitive meaning that it doesn’t distinguish between lower case and upper case for characters for text value lookups.
- A single MATCH Function only supports one dimensional range lookup, that is either vertical or horizontal. This means that you can’t combine vertical and horizontal ranges in a single MATCH function. You are however able to combine the MATCH Function with other functions such as the INDEX function to return a value within a two-dimensional range with both the row number and column number determined by 2 separate MATCH Functions.
- When using less than (1) approximate matches, you must arrange the lookup array in ascending order, whilst when using greater than (-1) approximate matches, you must arrange the lookup array in descending order.
MATCH Function Examples
Exact Match
In our example for this section, we have 10 different types of nuts, and we would use the MATCH function to return the position of one of the nuts.
Our task is to lookup the position of “peanuts” using the MATCH function. To achieve this, we use the formula below:
Approximate Match
For our approximate match, we have the list of continents and their population in millions sorted by descending order. We would be using the MATCH function to return an approximate match on the values.
Please note that because the data is sorted in descending order, our match type would be -1. To return the position, we would use the formula below:
Wildcard Match
For this section we have a combination of 2-digit state codes and potal codes of some cities in the United States. The 2-digit state codes and postal codes are separated by the dash delimiter (-), and our task is to use the MATCH Function to return the position of the state code.
The problem however is that the state codes are not standalone, we have some postal codes attached to each one and only separated by a delimiter. We used the same formula for exact match look up and it returned a N/A error.
This same formula will work only if we use the asterisk operator to inform excel to perform a wildcard lookup (meaning looking up any of the cells containing the lookup values regardless of other texts and numbers surrounding it).
We can put the asterisk in the cell F4 containing our lookup value, or we can manually type in the state code with an asterisk at the end.
=MATCH(“FL*”,B4:B13,0) // Returns 5
Integrating the Match Function in Financial Modeling
For our example in this section, we have different data plans offered by a telecommunications company, with 3 options for the validity period. Our marketing team is looking to promote some selected products to drive sales and we need to carry out an analysis to forecast the likelihood of the campaign turning out profitable.
The Finance Business Partners have been given the estimated incremental quantities to be sold for the products that are being targeted, and we need to use the INDEX and MATCH functions combination to retrieve their relevant price per unit.
Because there are two lookups to be done (horizontal lookup for plan and vertical lookup for price), we would need to use two MATCH functions to retrieve both the row number and column number for our INDEX Function.
Please note that because we need to make both row number and column number dynamic, we would select the entire table as our Index function array. This is the reason we selected Cells A4:D12 as our array.
The final step is to copy and paste the formula to cells J6:J8 and you are done.
