Lookup solution

[Melker]

Hi,

Have seen a problem that i do not know how to solve.

I have a small data base with information that i would like to "exctract
information from".
A function that would be prefect for the job would be someting like the
VLOOKUP-formula.

Now, here is the trick, the data is like this:

Country Customers Product Amount

USA No1 Sony 123
USA No 1 Sony 123
USA No 2 Ericsson 123
Sweden No 3 Sony 456
Sweden No 3 Nokia 456
Sweden No 3 BMW 456

Now you run in to a problem IF you want to have the vlookup formula search
customers for a customer
and then get the data for collum #3 to another sheet. The lookup formula
stops after the first "hit".
I would like to extraxt all the data for a specific customer to another
sheet.

How do you get the data in a situation like this ? Is it possible with
formulasor is there only more advanced a VBA solution?

Tankx

Melker

 

[Andy B]

Hi

If you need a total of the Amounts, you can try:
=SUMPRODUCT((A2:A7="USA")*(D27))
You can also add extra conditions eg.
=SUMPRODUCT((A2:A7="USA")*(C2:C7=Sony")*(D27))

Hope this helps

 

[Melker]

Andy,

You ar right, if you need the sum that would work, thank you.

But how to sort or move the data to another sheet is still unknown to me.

Tankx

Melker

 

[Andy B]

Have a look at this:
http://groups.google.com/groups?hl=...ft.public.excel.*&c2coff=1&safe=off&scoring=d

It may help your quest!!

 

[Melker]

Will look in to it ! Looks interesting!

Thank you

Melker