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).
M2, enter =MATCH(
- 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
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.