Boost your Excel skills by mastering the MATCH function, a powerful tool for efficiently locating positions within rows or columns. Learn its integration with VLOOKUP and INDEX to create dynamic and error-resistant spreadsheets.
Key Insights
- The MATCH function identifies the relative position of a lookup value within a single row or column and can be configured for exact or approximate matches using match types 0, 1, or -1.
- Combining MATCH with VLOOKUP enhances flexibility, automatically adjusting to table changes (such as inserted columns) by dynamically updating the column index number.
- MATCH is commonly paired with INDEX, known as INDEX-MATCH, providing a robust alternative to VLOOKUP that improves spreadsheet reliability and accuracy.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
The match function. The match function serves a single purpose. Its purpose is to find the relative position in a column or a row of a lookup value.
In some ways the match function is very similar to VLOOKUP. You're looking up information in a particular range. The difference here is the range can only be a column or a row.
One column or one row. It cannot be multiple columns and multiple rows. So that's a key distinction for the match function.
Match type is pretty much the same as range lookup in VLOOKUP. You'll either want an exact match or an approximate match. An approximate match will be either one or you get the added benefit of choosing negative one.
One finds the largest value based on ascending order. Largest value without going over. And then the descending order is the opposite.
You'll find the smallest value without going over and that is in descending order. Most of the time though you'll choose zero for an exact match. So the other thing you need to know about the match function is it's not going to return a value like VLOOKUP.
It's just going to return a position number. So it's not really useful unless you use it with other functions. You can use it with VLOOKUP.
You can also use it with index where it's commonly referred to as index match. All right so let's take a look at how this would work because other than me just telling you this, you'll actually have to see it put in practice. So I'm going to select the cell that's to the right of cantaloupe.
Cantaloupe is my lookup value. I want to find the position of cantaloupe within the row below. So I'll type in equal match.
I'm going to select cantaloupe. I'll enter a comma and then I want to select the row that contains cantaloupe. I'll enter a comma.
Cell reminds me here that if I want an exact match I'll choose zero for exact match. I'll press TAB. When I press ENTER I get five.
Now someone looking at this might think this is a useless endeavor. Why would I write a function to tell me something I can figure out just by looking at the sheet? I can count from one to five and see that cantaloupe is in the fifth position. Now counting in this case is very similar to what you do in VLOOKUP when you get to the column index number.
You count the number of columns. You need to move across the table to find the field that you're looking for. Well this is an opportunity to use match instead of manually counting.
The other benefit of using match is if in the VLOOKUP you've used the match function for the column index number. If someone inserts a column in your table the match function will automatically update to reflect the new column index number. So that makes that would make the VLOOKUP dynamic.
So that's another useful benefit of using the match function. In class we would tell students if you feel like you have an understanding of this please tackle task two. Use the match function to find the row position number that corresponds to France.
I'll type in equal match. I will select my lookup value which is France. I'll enter a comma.
Then I will go over and select the entire column. Again I want an exact match so that would be zero. When I press ENTER the answer is seven.
If I take a look at the column the United States is one then two three four five six seven and that is accurate. So that is the match function. Again you'll most likely use the match function with other functions to find a position number of a value in either a column or a row.