For this free Excel help article, for our example let us assume we have a table of sales data for a year for 6 salespersons in a company. Now let us see how using match we can pull out sales data from this table when a month and a salesperson’s name is provided.
The format of the match function is =match(lookup_value, lookup_array,[match_type])
Please note that the Match function returns a number which is a relative cell reference. In other words match will look up the lookup value within the lookup array and then it tells you how many cells away from the corner of your lookup array the looked up value is. This value i.e. the number may be ambiguous to you and that is why the Match function is used in conjunction with the Index function.
Let us study the Match and Index function with an example. Continuing with our example of the salespersons data we have the names of months in column A, starting from the A2 onwards till A13. B1 to G1 contains the names of the six salespersons, starting from SalesPerson1, SalesPerson2 till SalesPerson6. The sales data ranges from the cells B2 till G13.
Now in an unused portion of your spreadsheet create the following labels, one for Salesman, Month, Sales, Column and Row. Let us assume they are in the cells, J1, J2, J3, J4 and J5. Let us now use the match function to find out the column and row positions for Salesman2 and eventually find the sales of Salesman2 for that particular month.
First to find the column let us use the match function and insert it beside the column label, i.e. K5. The match function to use will be as follows.
Where K1 contains the salesperson, salesperson2, which is the lookup value. You can also directly enter the salesperson’s name here, the only constraint being it has to be in quotes.
B1:G13 – Is the lookup_array that includes the sales data including the header row containing the names of the salespersons.
Matchtype – can be 0, 1, or -1. We have used 0 because we want to lookup the first value that is exactly equal to the lookup value. 0 is also the ideal parameter for text values.
1 is for searching the largest value less than or equal to the lookup value.
-1 is for looking up the smallest value greater than or equal to the lookup value.
The result of this match function is 2 as the salesperson2 belongs to the column 2 from the array B1:G13.
Next we use the match function to determine the row. Let us say we want to find out the sales in May. We enter May into the cell K3, and our match function we insert in K6 is,
=match(K3,A2:A13,0) and this automatically returns the value 5 for row. Note that A2:A13 is the array of months where we want to look up our month i.e. May.
Now using these two pieces of information i.e row and column we can use the Index function to determine the sales for salesperson2 in the month of May.
In cell K4, we enter the following formula,
= index(B2:G13,K6, K5) where K6 is our row number and K5 is our column number as extracted by our match functions for row and column respectively that we saw earlier.
Where B2:G13 represents all the sales data and also our array and the formula would have returned the exact sales data for salesperson2 in May.
You can now play around using various salespersons and months and the correct sales value will automatically be computed by the match and index functions. Fun stuff? Excel consultants love this. This really is an important function to know, and for many it is frustrating and challenging. But we can help you with that, either thru our free Excel help articles, or our videos and downloads. If that does not do the trick, contact Christopher in the corporate office, located in the Irvine area of Orange County California, near Los Angeles, for paid assistance, or to just ask a question, on us.
And if you need help with Microsoft Excel 2013 (Excel 15), please let us know. We can upgrade your systems to take advantage of the changes..