How To Use The Excel Match Function

In our past posts, we have looked at different ways to use reference functions in excel. These are often used in order to help retrieve data in large sets. We have obviously looked at using the Vlookup function but also Sumif, Hlookup, etc. All of these can often be used but in some cases, having access to another function can also be quite helpful. In this case, we are looking at the match function in excel.

What is the Excel Match Function?

It is a function that gives you information about where a specific data point can be found in a database. For example, imagine that you had 10,000 lines in a file and were trying to find a specific data set. There are many different ways to get this done (as is always the case in excel it seems but the match function can give you an easy way to find out where a data lies in the spreadsheet. You can of course use nested functions in order to get additional data.

Suppose that you have a line of data and you need to know where you can find a specific fruit. Here is the data:

I could simply apply the following formula:

=MATCH(“peas”,A:A)

This would return: 10 which indicates that the value is in the line #10. It is not case sensitive (contrary to vlookup) which can also be helpful.

If you wanted to combine a nested function to find the price, we could use this new data:

By using the “offset function”, I can get the price as well:

=OFFSET(A1,MATCH(“peas”,A:A)-1,1)

As you can see, in this case, I could have used the vlookup function as well but it was more practical.

Hopefully this helps! As always, be sure to drop by with any comments or questions regarding this or any other excel function/issue!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************