The MATCH function is a very powerful tool for finding specific data within a large set of information in Excel.
Let’s assume you have the monthly sales values for your products for the last 12 months organized as in the following picture.
Imagine your range of products contains hundreds if not thousands of item and you want a fast and accurate way to find the sale value of a particular item in a particular month.
You can use the VLOOKUP function however VLOOKUP requires you to know and specify which column of a data range to pick the value from.
In this particular case you might want to choose Jun first and then explore the sales for Oct and so on.
Luckily the use of MATCH embedded within LOOKUP provides this flexibility.
The syntax of the MATCH function is:
- lookup_value is what you are looking for
- lookup_array is where you are looking for it
- [match_type] tells Excel which kind of match you want (e.g. exact)
In our example, by typing =MATCH(A9,B3:M3,0), we are asking Excel to look for the value of cell A9 (Jan) within the array B3:M3 (containing the 12 months) and look for an exact match. The result of this selection is 1 as Jan is found in the first cell of the array.
Once you create the MATCH function you can then incorporate it in a VLOOKUP function so that it specifies the Column that you want VLOOKUP to take into consideration.
This allows you to set two input cells up and use those input to provide great flexibility in finding the right information in a very short space of time.
Imagine if you had hundreds or thousands of rows and, instead of months, you had dozens and dozens of salespeople representing the columns!!
It would be a nightmare to quickly find out how many sales of Product 1289 John Carter made in July!
With VLOOKUP and MATCH your problem is solved!