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:

1. Select A3:G110.
2. Click in the Name box on the formula bar and enter UnitSales.
3. In J1, enter Which Product?
4. In K1, enter Which Month?
5. In L1, enter Product Row Number.
6. In M1, enter Month Column Number.
7. In N1, enter Total Unit Sales.
8. In L2, enter =MATCH (J2, A3:A110,0).
9. In M2, enter =MATCH(K2,A3:G3,0).
10. 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.

Print/View all Posts Comments on this article

Is this in all versions of excel? DanLM | 02/27/07

Definitely in 2000, maybe in 97 TechBro | 02/27/07

It's in Excel 97 SR-2 ... h3driver | 02/28/07

I'm lost kfisher@... | 02/28/07

Row and column headings wadde001@... | 02/28/07

Here's what I did DWC5 | 02/28/07

Screen Shots? ricklaroche@... | 02/28/07

screenshot smeenshot Neon Samurai | 03/08/07

Works in Excel 2003 for me DWC5 | 02/28/07

not sure champ5515@... | 04/19/07

It works, and is a good start crawk | 02/28/07

I'm Lost f.arsenio@... | 02/28/07

It Works already f.arsenio@... | 02/28/07

Maybe I'm showing my ignorance but . . . vbarrs@... | 03/02/07

pivot does not always offer the needed freedom Neon Samurai | 03/08/07

Update - no multiple returns with Index() Neon Samurai | 03/08/07