Excel’s MATCH formula is an extremely useful yet underutilized function within Excel’s toolkit of formulas. The reason people underestimate its value is because the MATCH formula’s primary objective is fuzzy and ambiguous. Without the proper context, its usefulness and potential applications are not obvious.
The MATCH formula’s fundamental purpose is to:
- Return the position of a value within an array
The syntax for the MATCH formula is listed below:
= MATCH ( lookup_value, lookup_array, [match_type] )
Assume I have an array of five numbers below and I need to find the position of the number 25.
By using the MATCH formula with the following inputs, I can fulfill that specific requirement.
Lookup Value: 25
Lookup Array: (select the array of numbers shown)
Match Type: 0 (tells Excel to look for an exact match to the number 25, not an approximate match)
Using these inputs, Excel will return the number 3, because 25 is in the third position within the array I selected. (The position numbers are inferred by the formula, and therefore, you don’t need a 1 through 5 label identifying any of the positions)
Based on this example, the mechanics of the MATCH formula are fairly simple. But again, the usefulness and practical application of the formula is still not obviously clear. How often do you really need to find the position of a value within an array? Having this particular need by itself is pretty rare.
Below, we’ll go through three examples of how best to put the MATCH formula to use. With the proper context, you’ll realize that the MATCH formula can be really handy in a number of situations.
Combination Formulas
Combination formulas are by far the most common way that people leverage the MATCH formula. All this means is that the MATCH formula is being used in conjunction with another more popular formula in Excel. For lookup formula combinations, you’ll almost always be using the exact match option for the match_type input.
When you combine a lookup formula with the MATCH formula, it provides Excel with a more dynamic way to perform the lookup. For example, if you use the VLOOKUP MATCH combination, the MATCH formula replaces the column_reference within VLOOKUP. Instead of having the column_reference be a static number, like “3“, you can use the MATCH formula to define what specific column you want VLOOKUP to pull from based on the column’s name.
Click here for a tutorial on VLOOKUP MATCH
Having the MATCH formula baked in also gives your lookup insert column immunity, which prevents your formula from erroring out if you need to insert a column within your table.
The most popular combination formula using MATCH is INDEX MATCH. Most people actually learn INDEX MATCH without really knowing what the MATCH formula actually does; they just memorize the required syntax. Regardless, the MATCH formula’s usefulness is proven by how frequently it appears in lookup combination formulas.
Below is a list of the most popular lookup formulas leveraging the MATCH formula. It includes vertical lookups, horizontal lookups (INDEX MATCH can be used horizontally), and matrix-style or two-way lookups.
Click here for a full list of lookup options, including the ones that don’t use MATCH
Check if a Value Exists Within a Data Set
One of the best ways to leverage the MATCH formula is just for checking if a value exists. The key to making this work is that you need to use the MATCH formula’s exact match option for the match_type syntax input.
In the example below, we have five lookup values where we want to check if they exist in our larger data set. All I have to do to perform this check is to right the MATCH formula for each of my numbers.
Lookup Value: (select the relevant lookup value)
Lookup Array: (select the data set array)
Match Type: 0 (tells Excel to look for an exact match)
If the value returns a position number, it means that it exists within your larger data set. If the value returns an error, then it’s not there.
The reason this is a great use of the MATCH formula is because it’s really intuitive. If a value does indeed exist within a data set, then the MATCH has to work for that value. Writing the MATCH formula provides you a quick and intuitive way to perform that check.
Now, there are faster ways to find if your value exists. For example, you could just use CTRL + F to use Excel’s Find feature. Additionally, you could just sort or filter your data and look for your value visually. However, the MATCH formula is probably the quickest way to perform this check for a list of multiple values, as shown in the example above. Just write your formula once, reference lock appropriately, and copy it down.
Alternative to Writing Nested IF Statements
We’ve already gone over two examples where we used the exact match option for match_type syntax of the MATCH formula. And when using MATCH, 95% of the time you’ll probably be using exact match. However, there is one alternative use case where the approximate match option makes sense. Particularly, this is to replace the need to write a Nested IF Statement.
Let’s look at all the possible match_type inputs available to dig into this further.
match_type Input | What Excel Does | lookup_array Sorting Requirements |
---|---|---|
1 or omitted | Finds the largest value in your lookup array that is less than or equal to lookup_value | Ascending order |
0 | Finds the first value in your lookup array that is exactly equal to lookup_value | None |
-1 | Finds the smallest value in your lookup array that is greater than or equal to lookup_value | Descending order |
The key with the approximate match option is in what it tells Excel to do:
Finding the largest value in your lookup array that is less than or equal to your lookup value.
If I need to assign a lookup value to an array of histogram bins (assuming my lookup array is the minimum value of those bins), approximate match does exactly that. See the example below where we are trying to assign 36 to a set of histogram bins.
Below is a completed example of assigning histogram bins with the MATCH formula. If you follow the logic through, you’ll see that the approximate match option allows you to assign histogram bins in a much more efficient way than using a Nested IF Statement. The key reasons that the MATCH formula is superior are:
- It’s much shorter and is easier to write than a Nested IF
- It’s significantly easier to audit when checking for mistakes
- The MATCH formula approach is scalable; if you need to add additional histogram bins, the formula doesn’t need to get any longer
Click here for the detailed tutorial on How to Use the MATCH Formula to Assign Histogram Bins
Conclusion
MATCH is a popular formula that many people use but rarely dig into the mechanics of. Despite its obtuse purpose (to return the position of a value within an array), the formula clearly has a number of viable uses within Excel. If you’ve only used the MATCH formula for lookup combinations (like INDEX MATCH), consider the other use cases I’ve mentioned to gain further leverage of this function.
If I want to lookup data from two columns such as a part number and the size or variant and return a value (cost) from another table of data in the same format. A vlookup you can only lookup one piece of data or 1 cell to return a value. Appreciate your help
@Billy:
You could utilize the Lookup Key generation from this post( http://www.mbaexcel.com/excel/how-to-use-the-anchored-countif-formula-in-excel/) to generate a single cell/reference for your 2-column lookup issue, then utilize index/match to reference the value returned from the first lookup, in a second lookup for the price.
Assuming you have a corresponding value in the price table that is also in the initial table…
Set Lookup Cell as = [Reference for Partnumber]&” “&[Reference for size/variant]
=Index(Range of prices, Match([Reference for value that appears in both tables to link your first lookup to your second lookup that pulls the price], Range of second table,0))
Would be easier to explain with some sample data, or via excel, rather than trying to explain without visual aids. The main key is to combine your two lookup criteria into a single one to get your reference, then use that reference for your second lookup – all of which can be combined into a nested lookup formula.
Hope this helps.