MATCH() match_type: -1 vs. 0 vs. 1

Software Microsoft Excel Advanced Excel Crash Course Section 11: Lookup and Conditional Calculations
3 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$180.54
You save:  A$51.58
List Price:  ৳11,866.68
You save:  ৳3,390.72
List Price:  CA$176.88
You save:  CA$50.54
CHF 88.56
List Price:  CHF 123.99
You save:  CHF 35.42
List Price:  kr856.85
You save:  kr244.83
List Price:  €115.16
You save:  €32.90
List Price:  £102.10
You save:  £29.17
List Price:  HK$1,085.16
You save:  HK$310.07
List Price:  ₹10,216.03
You save:  ₹2,919.07
List Price:  RM564.15
You save:  RM161.20
List Price:  ₦53,196.20
You save:  ₦15,200
List Price:  kr1,178.92
You save:  kr336.86
List Price:  NZ$194.68
You save:  NZ$55.62
List Price:  ₱6,730.59
You save:  ₱1,923.16
List Price:  ₨22,489.39
You save:  ₨6,426
List Price:  S$185.22
You save:  S$52.92
List Price:  ฿4,192.56
You save:  ฿1,197.96
List Price:  ₺1,033.78
You save:  ₺295.38
List Price:  B$750.37
You save:  B$214.40
List Price:  R2,095.15
You save:  R598.65
List Price:  Лв225.25
You save:  Лв64.36
List Price:  ₩154,078.01
You save:  ₩44,025.43
List Price:  ₪458.17
You save:  ₪130.91
Already have an account? Log In


Hi there. In one of our earlier examples, we had seen the theoretical application of match formula with an exact match. Before we really jump into the practical application, just one loose end to tie up. That is, when we use match with minus one or one. What do I mean by that? You'd have noticed in some of the previous videos that when I start writing match, after the second parameter, the third parameter gives you three options.

We had used only one which was zero, exact match, you had not taken care of one and minus one. So the theoretical level, how are they are to be applied. Let's take an example. Let's say there's a slab of scores. And I just have five categories more than equal to zero, more than equal to 40. Anybody who's schooled or touched more than 80, maybe more than 90, and more than equal to hundred, although it's 100 marks test but 100 is a cat So all these numbers are being read as more than equal to, I want to find out if the score would have been 81.

Under which category would it fall under? logically it should fall into this category. Why? Because it says more than or equal to 81. So 1234 is what I should be getting. So I write equal to match, oops, equal to match lookup value at one, comma.

For the time being, I am choosing only this data just to test how the answer going to react. I'm going to press f4 comma. Now since this is an ascending order, I'll be using one close the bracket enter. So I'm getting an answer three and why so because 81 has to be captured by the third value more than or equal to 80. But for the time being assumed that the score was 79. Since it is more than equal to 40, but has not touched a p it should be classified under two One, two.

So that's how it works. One is generally used when you have a data, which is placed in ascending order in the meaning of more than equal to. And with that we're going to use match one. Similarly, if you had a data, which was placed in less than equal format, now into law school, let me just put the age to help you understand is better had the age be less than equal to hundred or less than equal to 80 or 55 or 30? Or 15? How would I want the input to be placed at what sequence number?

So I write equal to match, look for the D one, comma, choosing the entire set of arrays starting from the first number. I'm going to fix it, comma, since it is in descending order the entire list I'll be pushing for minus one. I close the bracket and let me see what I get. I'm getting one. Let's understand why. Since 81, is less than or equal to one But it has not touched at or any number below it, it is the first instance under which it has been classified.

Now imagine if I put at age zero, the moment it touches 80 It must be classified under second category. If I give 75 it still will be under category two. But if I give 54 let's see what happens. So eventually as we proceed in our program, we'll see what are the practical applications of the 01 minus one and overall, where match can be used. As of now, the bottom line is match is used to find the sequence number, the position number for second or third in a given set of one dimensional array. It never works with a two dimensional or table array.

Sign Up


Share with friends, get 20% off
Invite your friends to TabletWise learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.