Lookup Formulas
How to Use INDEXMATCH, Part 2: The
MATCH Function
The MATCH function makes the INDEX function useful.
This is the second of a threepart series designed to
INDEXMATCH clearer.
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

In part 1 of
this series about INDEXMATCH, I began the article with
these two examples:
Formula 1: =INDEX(Product,2,1)
Formula 2: =$B$4 I explained that in the specific workbook I was using, both
formulas returned exactly the same result.
Well, soon after Excel was first introduced, decades ago, I was
talking with the
Excel Program Manager on the phone. With examples like those two
formulas in mind, I told him that INDEX was a
useless function because it requires that we enter numbers for
the row and column arguments.
"If we know what row and column numbers to use," I said, "we
could just as well enter the cell address. So what's the point
of INDEX?"
He was very patient with me. He said, "Charley, have you
looked at the MATCH function? We created MATCH specifically to
work with INDEX."
Well, as Excel's head honcho advised me to do years ago,
today we're going to take a look at the MATCH function...a
function that was SPECIFICALLY created to work with INDEX.
(This link lets you
download the
zip file of all three example workbooks here.)
MATCH is a lookup function, like VLOOKUP, HLOOKUP, and
LOOKUP. But unlike those other functions, MATCH doesn't return
the value it finds. Instead, MATCH returns the position of a
lookup value in a single row, column, or onedimensional array.
To illustrate, if your lookup value is the third item in your
lookup array, MATCH returns 3. Or if your lookup value is in the
67th position in your lookup array, MATCH returns 67.
Here's the syntax of the function...
=MATCH(lookup_value, lookup_array, match_type)
...where...
 lookup_value is the value you want to
find.
 lookup_array is a single row or column,
or a onedimensional array, where MATCH should look for the
lookup_value.
 match_type is one of three
numbers that specifies how your data is sorted and how MATCH
is to perform.
Here are short explanations about the three numbers you can enter for the match_type...
Match_Type = 0. This is the type
you'll use about 95% of the time. It returns the position of the
first match found in your lookup_array, no
matter how your lookup_array is sorted. If the lookup_value
isn't in your lookup_array, MATCH returns #N/A.
Match_Type = 1. Use this type only for
specific reasons, and only with data sorted in
ascending order. If the data isn't in ascending order, MATCH can
return incorrect results or incorrect #N/A values. If the
lookup_value isn't in your lookup_array, MATCH returns the position of the
largest value that's less than or equal to the lookup_value.
(I'll show you a examples shortly.)
Match_Type = 1. I don't think I've
ever found a reason to use this match_type for real work. You use this
type only for data sorted in descending order. If the
lookup_value isn't in your lookup_array, it causes MATCH to
return the position of the smallest value that's greater than or
equal to the lookup_value.
Let's take a look at some examples...
Match Type = 0
This is the type you'll use about 95% of the time. It returns
correct results no matter how your lookup_array is sorted. If
the lookup_value isn't in your lookup_array, MATCH returns #N/A.
This
example uses an unsorted table. That is, the data is in a random
order.
Formula 3: =MATCH(6,Test,0)
[Result = 3]
MATCH works its way down the Test column until it finds 6,
and returns 3, indicating that the value 6 is the third item in
the list.
Formula 4: =MATCH(7,Test,0)
[Result = #N/A]
MATCH looks through the entire list, but is unable to find
the value 7. And therefore, it returns #N/A.
MATCH
also works with text, no matter how it's sorted...
Formula 5: =MATCH("x",Test,0)
[Result = 5]
By this time, there should be no surprise. This formula
searches for "x" and tells us that it's the 5th item in the
list.
Formula 6: =MATCH("m",Test,0)
[Result = #N/A]
And here, we're searching for an item that's not in the list,
and MATCH reliably gives us an #N/A value.
Match Type = 1
Use this match_type only for specific reasons, and always
with data sorted in ascending order. If the data isn't in
ascending order, MATCH sometimes returns incorrect results or incorrect
#N/A values.
One
common reason for using a match_type of 1 is for use with a
pricediscount table, as I'll show you in Part 3 of this series.
Here are some examples from the sorted table...
Formula 7: =MATCH(4,Test,1) [Result
= 2]
When your data is sorted correctly, using a match_type of 1
gives you reliably correct results.
Formula 8: =MATCH(3,Test,1)
[Result = 1]
Here's
one way to remember how MATCH works when it searches for data
that's not in the table: MATCH finds the nextlargest value, and
then backs up one position.
Formula 9: =MATCH("m",Test,1)
[Result = 3]
Even though the text is sorted correctly, I can't think of a
reason why you ever would use a match_type of 1 with text. In
Formula 9, for example, we search for "m" and MATCH (correctly!)
returns the position for "f".
Match Type = 1
You use this type only for data sorted in descending
order...something I've never found a reason to do.
However,
if you ever do need to look up data that's sorted in decending
order, MATCH is
your only option. Neither VLOOKUP or LOOKUP
offers that ability.
Here are two quick examples...
Formula 10: =MATCH(6,Test,1)
[Result = 2]
That is, the value 6 is listed second in this list.
Formula 11: =MATCH(3,Test,1)
[Result = 4]
When the lookup_value doesn't exist in the list, MATCH with a
match_type of 1 returns the position of the smallest value
that's greater than or equal to the lookup_value. That is, it
finds the first value that's smaller than the lookup_value, and
then it backs up one position.
In part 3 of this series, I explain
six
INDEXMATCH lookups that VLOOKUP can't do.
If you want to play with more example formulas from all three
articles about INDEXMATCH, you can
download the
zip file of all three example workbooks here.
