Modified lookup function

**maacmaac** · 10-09-2009, 12:02 PM

This is a two-part question.

I have spreadsheet that I use to display quarterly metrics. Within the workbook, there are two spreadsheets; (1) output (2) data.

Part 1: I want the ability to view data for any quarter of any year. In columns O-P, I have created a dropdown for the year I want to view and option buttons for the quarter I want to view.

The data is organized in one sheet and includes all information by quarter.

As an example, say I want to display data for District 112 and I want to display data for the 3rd Quarter of 2007. I am trying to create a formula that would look in the table as follows: (1) look for district 112 then (2) look for 2007 then (3) look for 3rd quarter then (4) look for units sold. I tried using a modified lookup function but didn’t have any luck.

Part 2: The second part of my question is similar to the first part but in this case I need to summarize data (cells $B$18:$D:$22). As an example, say I want to display the total number of sales in the U.S. for the 3rd quarter of 2007. In this example, I would look in the table for the following: (1) look for Division then (2) look for 2007 then (3) look for 3rd quarter then (4) sum all based on criteria.

I have attached a sample for better clarification. Thank you in advance for any comments.

**JBeaucaire** · 10-09-2009, 01:15 PM

B4 can be simplified down to:
="United States - " & LOOKUP($P$21,{1,2,3,4},{"1st","2nd","3rd","4th"}) & " Quarter "&$P$20

D6:
=SUMPRODUCT(--(Data!$B$2:$B$78=B6),--(Data!$E$2:$E$78=$O$5),--(Data!$F$2:$F$78=$P$21), Data!$D$2:$D$78)
...then copied down and across to other cells in upper section, will self-adjust as copied.

C19:
=SUMPRODUCT(--(Data!$A$2:$A$78=$B19),--(Data!$E$2:$E$78=$O$5),--(Data!$F$2:$F$78=$P$21), Data!$D$2:$D$78)
...copied down.

D19:
=SUMPRODUCT(--(Data!$A$2:$A$78=$B19),--(Data!$E$2:$E$78=$P$24),--(Data!$F$2:$F$78=$P$25), Data!$D$2:$D$78)
...copied down.

**maacmaac** · 10-09-2009, 03:32 PM

That is awesome. Works great.

Just one more question. The data is dynamic so I am adding additional rows every quarter.

The current formula goes to row 78, which is where the data stops. I tried changing the code in D6 from:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

When I try this, I get a #NUM! error. Is there a way to account for a dynamic table without having to change the code every time? Thanks again for your comments.

**JBeaucaire** · 10-09-2009, 07:35 PM

You cannot use entire columns with SUMPRODUCT(). Just a warning, too, SUMPRODUCT() comes with a lot of overhead, so I really hope you aren't aiming to create 100s and 100s of these formulas.

In the meantime, expand your formula in a more controlled way. Expand to enough to cover your needs for a year or so. RESIST putting in a range of 20000 rows if you don't need it.