The Excel Match Function: Ask and You Shall Receive

excelmatchTriumph.  Exhilaration.  Relief.  These are the feelings you experience when you find something you’ve lost, which, whether or not you doubt the emotional capacity of Excel, is exactly what the Excel Match function can do for you.  In its purest essence, the Match function searches a range of cells for a specified item, and then identifies its relative position.  You would use the Match function in place of the Lookup function when you want the location of an item and not the item itself. It can’t bring it to you, but it can help you find it.  You can use the Match function to find lost values, but you can also use it within other functions to provide location values.  We’ll take a closer look at the Match function’s syntax, and then we’ll put it to use to see if it lives up to the hype.  A basic Excel course will show you this is just one of the many functions at your fingertips.

For all its divine glory, the Match function is a simple one.  Its syntax is as follows:

Screen Shot 2013-12-14 at 2.57.11 PM

Now let’s break down the arguments (jeez, this really is getting biblical):

  • lookup_value    The lost item; the value that you want to “match,” as it were.  If you had an enormous inventory of tempting objects and wanted to know, say, how many apples you had, you would be using “apples” as the look-up value, while the number of apples is the actual value desired.  Note: the lookup_value can be a number or text (if text, use quotation marks), or a cell reference to a number or text.
  • lookup_array    Simply the range you want to search.
  • match_type    Not required, but there are three possibilities: -1, 0, and 1 (1 is the default).  You would use the match_type argument if you wanted to specify how Excel matches the lookup_value with the lookup_array.

While not required, the match_type argument is where you can add a little variety to the Match function.

  • A “1” (or blank) matches the largest value that is less than or equal to the lookup value.  The only stipulation is that the values in the lookup array are in ascending order (for text, in alphabetical order; Apples, Chocolates, Ferraris, etc.).
  • A “0” finds the exact match of the lookup value, and in this case the values in the lookup array can be in any order.
  • A “-1” matches the smallest value that is greater than or equal to the lookup value.  As you might expect, the values in the lookup array must be in descending order (for text, in reverse alphabetical order; Ferraris, Chocolates, Apples, etc.).

Ok.  Let’s have a look at our tempting objects spreadsheet:

Screen Shot 2013-12-14 at 1.59.21 PM

California is crazy these days, and bacon is flying off the shelves.  Let’s locate Bacon and see how our inventory is holding up:

Screen Shot 2013-12-14 at 3.02.34 PM

The Match function tells me that Bacon is fifth in my list of tempting objects (if you need help quitting pork, use this incredible course to learn how to hypnotize yourself):

Screen Shot 2013-12-14 at 2.00.06 PM

Now, let’s use the match type “1” to see if I have 10 of anything:

Screen Shot 2013-12-14 at 3.07.17 PM

It turns out that I don’t, but now I know where the next lowest value resides:

Screen Shot 2013-12-14 at 2.01.05 PM

We can do the same thing with “-1” if we want to see the next highest value:

Screen Shot 2013-12-14 at 3.09.49 PM

Or can we?  We get an error because the lookup array is not in descending order.  But that’s ok.  We can use this as an excuse to review, as well as introduce of few of the nitty-gritty details of the Match function (you can learn other essential functions in this advanced Excel training course).

  • For starters, the Match function finds the relative position of the matched value within the lookup array (or range), not the value itself.  Example: =MATCH(“Apples”,B2:B7,0) would return 1, meaning Apples is in the first position of my range.  If the lookup array is not ordered appropriately relative to the function, it will return #N/A.
  • When match type is 0 and the lookup value contains text: use a question mark (?) to match a single character, and an asterisk (*) to match a sequence of characters.  If you’re trying to match a question mark or asterisk, precede it with a tilde (~).
  • You cannot use the Match function to distinguish between uppercase and lowercase letters.
  • If you have a list that contains duplicate values, the Match function can only find the first occurrence of the value you are matching.  You would use a match type of “0” for an unsorted list, but this would still yield only the first occurrence.  You would use a match type of “1” for a list in ascending order if you wanted to find the last occurrence of the match value (and using a “-1” for a list in descending order would also find the last occurrence).

The Match function may not help you find your cars keys, but it can be used in a variety of situations and will make you drastically more efficient when searching through long lists.  If all this searching has you excited, you might want to think about search from another angle: why not diversify your talents and learn everything essential about search engine optimization?  “It’s SEO easy!”