Analyze Excel data with the Match function

by Mary Ann Richardson | Feb 27, 2007 8:00:00 AM

Tags: Microsoft Office, Mary Ann Richardson, Microsoft Excel, Microsoft Office Suite Excel Tips Newsletter

Takeaway: You can use Excel's Match function to scan data for certain parameters, such as a specific product sold in a particular month. Mary Ann Richardson narrows down the process in this Excel tip.

Management would like a quick and easy way to find total unit sales for any given product during any given month. They are currently spending a great deal of time scanning an Excel spreadsheet that lists unit sales of each of the company's products for the first six months of 2006 in A3:G110. Follow these steps to create a formula that will scan the data automatically for them:

Select A3:G110.
Click in the Name box on the formula bar and enter UnitSales.
In J1, enter Which Product?
In K1, enter Which Month?
In L1, enter Product Row Number.
In M1, enter Month Column Number.
In N1, enter Total Unit Sales.
In L2, enter =MATCH (J2, A3:A110,0).
In M2, enter =MATCH(K2,A3:G3,0).
In N2, enter =INDEX(UnitSales,L2,M2).

The managers just need to enter the name of the product they are looking for in J2 and the Month for which they need the sales data in K2. The answer will appear in N2.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

16 comment(s)