combo lookup using parameters?!

[nadirv8]

Hi all, I'm quite new when it comes to using more than basic functions in Excel so im hoping some legend could help me out.

Basically I want that Excel provides the figure adjecent to all of the parameters I choose... For example, this is my list:


CELL.........A..............B..............C..............D.............E
1..............France.......AOP..........2005.........Q2...........$100
2..............France.......AOP..........2005.........Q4...........$105
3..............France.......AOP..........2006.........Q2...........$120
4..............France.......AOP..........2006.........Q4...........$125
5..............France.......ACT..........2005.........Q2...........$90
6..............France.......ACT..........2005.........Q4...........$130
7..............France.......ACT..........2006.........Q2...........$89
8..............France.......ACT..........2006.........Q4...........$119


So far I have a series of drop down boxes to choose the parameters (columns A - B) but I don't know how to get Excel to extract the info from column E based on the parameters.
Beer for someone who can tell me how

N

 

[Andrew Poulsom]

Couldn't you use a Pivot Table with columns A and B as Page fields?

 

[hatman]

You're likely to get several answers witha beer in the offing...

I'll choose one of the more basic (aside from Data->Filter->Autofilter)

Use Sumproduct(). Assuming that your drop-downs are in G1:J1 respectiveely, the formula would look something like:

=sumproduct(--(g1=a1:a8),--(h1=b1:b8),--(i1=c1:c8),--(j1=d1:d8),e1:e8)

 

[Peter_SSs]

nadirv8

Welcome to the Mr Excel board!

Where do you want the 'extracted' results? What do you want to do with theM?

Are you familiar with the AutoFilter tool? Perhaps you could Filter columns A and B and your result would be clearly visible without any 'extraction'.

If the suggestions so far are not what you want, post back with some more detail.

You might also want to investigate Colo's HTML Maker for displaying sheet samples on this board. Here's how:
http://www.mrexcel.com/board2/viewtopic.php?t=92622

 

[nadirv8]

Ah, This would be a tool for my directors to use to compare results and figures between months, countries etc. The list is FAR bigger.

The idea is: the user chooses which 2 or 3 sets of parameters, and Excel pulls and compares the figures.

IMO the pivot table would work for me but not for someone who needs it to be completely fed to them

Equally the filter would be good for my use however not for someone who'd get "confused." You'd be supprised how useless managers are who are earning well into £££,£££.

Basically, I know how to create buttons and actions within that... thats to say I've recorded and attached macros. But im pretty stumped here.

 

[hatman]

Did you try Sumproduct? (see above)

 

[nadirv8]

Did you try Sumproduct? (see above)

Thankyou Hatman, worked perfectly!

IM your address and beer of your choice and il send to you! May I suggest FAXE, a Danish 5% premium lager which comes in a 1L Aluminium can?

 

[hatman]

Did you try Sumproduct? (see above)

Thankyou Hatman, worked perfectly!

IM your address and beer of your choice and il send to you! May I suggest FAXE, a Danish 5% premium lager which comes in a 1L Aluminium can?

My preference leans more toward Stouts and Porters, though a 9% Ale on Lees tastes mighty fine now and again... and it's been almost 10 years since I have had the pleasure of a Wee Heavy Ale (yum!). You realize now I'm REALLY gonna have to stop at the nearset Packy that carries imports and microbrews on the way home :wink: