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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 12, 2006
Messages
9

ADVERTISEMENT

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.
 

nadirv8

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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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
Top