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

by Matthew Kuo on December 5, 2015

in Database Theory, Excel, Formula Writing

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

Bill Gates MATCH Excel

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.

How to Use MATCH 01

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)

How to Use MATCH 02

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

How to Use MATCH 03

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 .  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)

How to Use MATCH 04

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 InputWhat Excel Doeslookup_array Sorting Requirements
1 or omittedFinds the largest value in your lookup array that is less than or equal to lookup_valueAscending order
0Finds the first value in your lookup array that is exactly equal to lookup_valueNone
-1Finds the smallest value in your lookup array that is greater than or equal to lookup_valueDescending 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.

How to Use MATCH 06

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

How to Use MATCH 07

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 ), consider the other use cases I’ve mentioned to gain further leverage of this function.

{ 2 comments… read them below or add one }

Billy Cukman May 26, 2016 at 4:32 pm

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

Reply

Mike H October 27, 2016 at 3:18 pm

:

You could utilize the Lookup Key generation from this post( http://rusinfomos.ru/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.

Reply

Leave a Comment

{ 1 trackback }

  • December 5, 2015

Previous post:

Next post:


Related pages


ucla anderson alumni databaseexcel formula for contains a worducla gshiphow to pick random numbers in excelexcel compare rangesthe goal by eliyahu goldratt sparknotesexcel vba accounting formatvlookup table rangeirs w4 calculatoradding sums in excelexcel auto multiplyhow to use vlookup in excel 2010 step by stepmaking excel graphsmultiple vlookup in excelexcel formula for finding duplicateshow to delete an array in excelcolumn offset vbazs associates employee benefitscomma separator excelexcel string comparisonwhat is vlookup function in excelexcel shortcuts keys listtesting normality in excelexcel axis titleexcel format in thousandsexact bins lookupexcel inventory trackercourses on excel spreadsheetsvlookup based on multiple criteriatest normality excelgaussian excelhow to match cells in excelbuild a macro in excelnormal plot excelirs penalties calculatorexcel nested if functionsexcell countifaccenture management consulting career pathhow to delete a row in excel100 stacked column chart excelcheck symbol in ms wordhistogram template excelcomma separator excelusing a lookup table in excelvlookup dan hlookupvlookup reverselookup vs vlookuprandom name picker generatorhow to create a column graph in excelexcel vlookup range of valuesmicrosoft excel histogramhow to write an if statement in excelinsert histogram in excelexcel if statement not equalinsert tick excelexcel remove duplicate columnsms excel formulas and functionsmeaning of vlookup and hlookupround to nearest thousand in excelexcel if function with multiple conditionsrange lookup excelwhere is tick symbol in excelmicrosoft office free trial 60 daysquestions to ask at mba interviewexcel bar graph multiple seriesmultiple criteria vlookuptax planning spreadsheetcompare columns in excelhow do you use vlookupdifferent types of formulas in excelexcel countif in range
\n