Excel Lookup Function ????

[pgh2377]

Here is what I'm trying to do. I have a worksheet with three columns: Plan / Category / Sales. As shown:

PLAN CATEGORY SALES
2 ASTHMA $15
2 MS $20
3 ASTHMA $23

What I'm trying to do is have a summary page where the user can type in a plan selection box, say 2 and on a summary underneath with the Cateogies already listed the sales autmoatically populate. For example, the user selects 2, and in the Asthma and MS boxes the sales of $15 and $20 appear. Is this possible? I know I can use a lookup function for the plan, but how would it produce the Asthma sales corrrectly?

 

[KenWright]

Have you considered just using Data / Filter / Autofilter in situ?

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[pgh2377]

I did, however I have a combo box that supplies the selection plans, and created cells mark Asthma, MS, etc.. and a total cell, so I was trying to create a lookup summary, but it seems impossible with using filters

 

[bluedragon2]

The SUMPRODUCT Formula would work for you:

=SUMPRODUCT((<PLAN array>=<lookup value>)*(<CATEGORY array>=<Category Cell>)*(<SALES array))

For example if the sales data is on sheet 1 A1:C100 and on sheet 2 cell A1 I have the PLAN I want to lookup and B1 has the Category ASTHMA then in C1 I would put:

=SUMPRODUCT((Sheet1!A1:A100=A1)*(Sheet1!B1:B100=B1)*(Sheet1!C1:C100))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

For an explanation on how SUMPRODUCT works, see FAQ68-4725.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

The other option is to sort the data on the Category function and then use Data / Subtotals, summing the sales field.

This also has the benefit of allowing you to see a top level view summarised by area, but instantly exapandable by clicking on the little plus sign for each area to see the detail data.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------