Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add text to meet criteria | Excel Worksheet Functions | |||
count rows that meet certain criteria | Excel Discussion (Misc queries) | |||
add numbers if they meet criteria...? | Excel Worksheet Functions | |||
How to calculate how many meet min and max criteria | Excel Worksheet Functions | |||
Count Cells that meet Criteria | Excel Worksheet Functions |