Vlookup when have 2 or more criteria to meet
 

I have a huge database with the sales of different product lines and regions
and I need to extract the key lines by region for the corresponding period.
vLookup is just bringing the first "world" in the database so if I want to
have the value for world for "cosmetics" as well as world for "analgesics".
(Criteria Region and Criteria Product Line)
I'm not a heavy excell user....so I guess I have to combine to functions how
can I do it?? HELP!!!!!

Column #
1 2 3 4 5
Prior Year Budget Actual
World Cosmetics $XX $XX $XX
World Analgesics $XX $XX $XX
Europe Cosmetics $XX $XX $XX
Europe Analgesics $XX $XX $XX
Asia Cosmetics $XX $XX $XX
Asia Analgesics $XX $XX $XX

Vlookup when have 2 or more criteria to meet
 

try the formula below, see this example:
Make Month Price
Ford June 7,500
Ford June 8,000
Ford May 6,873
Ford June 11,200
Renault June 13,200
Renault June 14,999
BMW June 17,500
BMW May 23,500
BMW June 18,000

Sum with two conditions:

Ford June 26,700
=SUMPRODUCT((A4:A12=A14)*(B4:B12=B14)*C4:C12)
What's the value of the Fords sold in June?



"Angie" wrote:

I have a huge database with the sales of different product lines and regions
and I need to extract the key lines by region for the corresponding period.
vLookup is just bringing the first "world" in the database so if I want to
have the value for world for "cosmetics" as well as world for "analgesics".
(Criteria Region and Criteria Product Line)
I'm not a heavy excell user....so I guess I have to combine to functions how
can I do it?? HELP!!!!!

Column #
1 2 3 4 5
Prior Year Budget Actual
World Cosmetics $XX $XX $XX
World Analgesics $XX $XX $XX
Europe Cosmetics $XX $XX $XX
Europe Analgesics $XX $XX $XX
Asia Cosmetics $XX $XX $XX
Asia Analgesics $XX $XX $XX

Vlookup when have 2 or more criteria to meet
 

Have you considered a Pivot Table?

http://peltiertech.com/Excel/Pivots/pivotstart.htm



In article ,
Angie wrote:

I have a huge database with the sales of different product lines and regions
and I need to extract the key lines by region for the corresponding period.
vLookup is just bringing the first "world" in the database so if I want to
have the value for world for "cosmetics" as well as world for "analgesics".
(Criteria Region and Criteria Product Line)
I'm not a heavy excell user....so I guess I have to combine to functions how
can I do it?? HELP!!!!!

Column #
1 2 3 4 5
Prior Year Budget Actual
World Cosmetics $XX $XX $XX
World Analgesics $XX $XX $XX
Europe Cosmetics $XX $XX $XX
Europe Analgesics $XX $XX $XX
Asia Cosmetics $XX $XX $XX
Asia Analgesics $XX $XX $XX

Vlookup when have 2 or more criteria to meet
 

Hi Bill thanks but is not what I'm looking for, I don't need any math
operation. I just need to pick from the database the values that meet the
criteria Region & Product.
That means 2 criteria for the vlookup: i.e.
(Criteria 1) region = Europe (Criteria 2) product line = Cosmetics
So the vlookup will bring me the information from the database:

Region P.Line Yr2006 Budget Yr2007

Europe Cosmetics 2.000 2.100 1.800
Asia Cosmetics 50 50
48
N.America Cosmetics 3.000 3.000 3.200
L.America Cosmetics 500 600 550

I need the get the values for Year 2006/Budget/Y2007 for the 11 product
lines split by region. So the only thing I need is to know how to build the
VLOOKUP formula in that way that picks the region and the product line.
__________________________________________________ __________

"billy" wrote:

try the formula below, see this example:
Make Month Price
Ford June 7,500
Ford June 8,000
Ford May 6,873
Ford June 11,200
Renault June 13,200
Renault June 14,999
BMW June 17,500
BMW May 23,500
BMW June 18,000

Sum with two conditions:

Ford June 26,700
=SUMPRODUCT((A4:A12=A14)*(B4:B12=B14)*C4:C12)
What's the value of the Fords sold in June?



"Angie" wrote:

I have a huge database with the sales of different product lines and regions
and I need to extract the key lines by region for the corresponding period.
vLookup is just bringing the first "world" in the database so if I want to
have the value for world for "cosmetics" as well as world for "analgesics".
(Criteria Region and Criteria Product Line)
I'm not a heavy excell user....so I guess I have to combine to functions how
can I do it?? HELP!!!!!

Column #
1 2 3 4 5
Prior Year Budget Actual
World Cosmetics $XX $XX $XX
World Analgesics $XX $XX $XX
Europe Cosmetics $XX $XX $XX
Europe Analgesics $XX $XX $XX
Asia Cosmetics $XX $XX $XX
Asia Analgesics $XX $XX $XX

Vlookup when have 2 or more criteria to meet
 

Angie,

Not sure this is exactly what you want either but if there is only one value
for each region product combinatino under each column (yr2006, budget, and yr
2007) you can use this type of formula.
=IF(AND($C$2:$C$9="EUROPE",$D$2:$D$9="COS"),E2,"") .
Just make one formula for each region product combination by changing the
name and copy to colums F and G.

C D E F G
europe cos 2000 2100 1800
asia cos 50 50 0
north cos 3000 3000 3200
latin cos 500 600 550
europe abc
asia abc
north abc
latin abc


"Angie" wrote:

Hi Bill thanks but is not what I'm looking for, I don't need any math
operation. I just need to pick from the database the values that meet the
criteria Region & Product.
That means 2 criteria for the vlookup: i.e.
(Criteria 1) region = Europe (Criteria 2) product line = Cosmetics
So the vlookup will bring me the information from the database:

Region P.Line Yr2006 Budget Yr2007

Europe Cosmetics 2.000 2.100 1.800
Asia Cosmetics 50 50
48
N.America Cosmetics 3.000 3.000 3.200
L.America Cosmetics 500 600 550

I need the get the values for Year 2006/Budget/Y2007 for the 11 product
lines split by region. So the only thing I need is to know how to build the
VLOOKUP formula in that way that picks the region and the product line.
__________________________________________________ __________

"billy" wrote:

try the formula below, see this example:
Make Month Price
Ford June 7,500
Ford June 8,000
Ford May 6,873
Ford June 11,200
Renault June 13,200
Renault June 14,999
BMW June 17,500
BMW May 23,500
BMW June 18,000

Sum with two conditions:

Ford June 26,700
=SUMPRODUCT((A4:A12=A14)*(B4:B12=B14)*C4:C12)
What's the value of the Fords sold in June?



"Angie" wrote:

I have a huge database with the sales of different product lines and regions
and I need to extract the key lines by region for the corresponding period.
vLookup is just bringing the first "world" in the database so if I want to
have the value for world for "cosmetics" as well as world for "analgesics".
(Criteria Region and Criteria Product Line)
I'm not a heavy excell user....so I guess I have to combine to functions how
can I do it?? HELP!!!!!

Column #
1 2 3 4 5
Prior Year Budget Actual
World Cosmetics $XX $XX $XX
World Analgesics $XX $XX $XX
Europe Cosmetics $XX $XX $XX
Europe Analgesics $XX $XX $XX
Asia Cosmetics $XX $XX $XX
Asia Analgesics $XX $XX $XX

Vlookup when have 2 or more criteria to meet
 

Actually I will have to do that, although I would prefer not .......as I'm
not working in a regular excell, but with an application (Citrix based) and
the performance of the system tents to be slower when using Pivot Tables, so
that's why I tried to use lookup options considering that I have 11 principal
product lines and 5 regions, but my database includes other lines and other
regions that I don't want to include in the report.
Thanks anyway. I will have to live with the slow performance and build the
pivot table.
Thanks


"JE McGimpsey" wrote:

Have you considered a Pivot Table?

http://peltiertech.com/Excel/Pivots/pivotstart.htm



In article ,
Angie wrote:

I have a huge database with the sales of different product lines and regions
and I need to extract the key lines by region for the corresponding period.
vLookup is just bringing the first "world" in the database so if I want to
have the value for world for "cosmetics" as well as world for "analgesics".
(Criteria Region and Criteria Product Line)
I'm not a heavy excell user....so I guess I have to combine to functions how
can I do it?? HELP!!!!!

Column #
1 2 3 4 5
Prior Year Budget Actual
World Cosmetics $XX $XX $XX
World Analgesics $XX $XX $XX
Europe Cosmetics $XX $XX $XX
Europe Analgesics $XX $XX $XX
Asia Cosmetics $XX $XX $XX
Asia Analgesics $XX $XX $XX