Popular Functions in Excel: Lesson 8 – “MATCH”
3 minute read
Welcome to the eighth and final part of our miniseries on the most popular Excel functions. We’ll cover the “MATCH” function, which gives you the position of an item in a range.
If you missed the other parts of our series, check out part one (“IF”), two (“SUM”), three (“COUNTIF”), four (“VLOOKUP”), five (“SUMIF”), six (“LOOKUP”), and seven (“CONCATENATE”).
So when we type =MATCH, you can see right away that we’re going to need to create three terms within our function to tell it what we want it to do.
The first one, lookup_value, is fairly self explanatory–this is where you specify what value you want the function to find. Let’s say we’re looking for invoice #10050, so we’ll enter in 10050.
Next up is the lookup_array, which is the range of cells you want to search. So we’re going to select the cells in Column A, telling Excel that this is where we’re looking for our value.
Now, this last term is a little different than those we’ve covered before. This is where you have to specify a match_type. This is a code that tells Excel what you want it to do. If you enter nothing here, it will default to 1, or you can enter one manually. This means that the match function will find the largest value that is less than or the same as the term that you wanted to search for. If you enter 0 instead, the MATCH function is only going to find exact matches. On the other hand, if you enter -1, it will look for the smallest value that is larger than your search term.
Then, once you close the parentheses and hit Enter, it will provide you with the location of the item that you’re trying to find. We got a 6 because invoice # 10050 is the sixth item in the range.
Now, if you get some errors when using the match function, the sorting of your data is something you need to consider. If you’re using the 1 match function, you need to make sure your data is sorted in ascending order, and in descending order if you want to use -1.
So essentially, MATCH is a way of searching your sheets. The match function gives you the position within the array that we selected in the second term of the function, so that you can find data that you’re looking for anywhere in the sheet.
