In this three-part series, I’m showing you how to use INDEX-MATCH. In Part 1, I showed you how to use the INDEX function. Here, in Part 2 of this series, I’ll show you how to use the MATCH function. And in Part 3, I’ll show you six INDEX-MATCH lookups that VLOOKUP can’t do.
In Part 1, 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.
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 one-dimensional 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 one-dimensional 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 price-discount 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 next-largest 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 isyour 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 INDEX-MATCH lookups that VLOOKUP can’t do.
