How to Use the MATCH Formula in Excel: Three Practical Examples

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.

• INDEX MATCH
• VLOOKUP MATCH
• INDEX MATCH MATCH
• OFFSET MATCH MATCH

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.