combo lookup using parameters?!

nadirv8

New Member
Joined
Sep 12, 2006
Messages
9
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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:
Code:
=sumproduct(--(g1=a1:a8),--(h1=b1:b8),--(i1=c1:c8),--(j1=d1:d8),e1:e8)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,217,382
Messages
6,136,239
Members
450,000
Latest member
jgp19

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top