|
Lookup or Match with rows and column together
i would like to look up a value from worksheet2 based on cells matching the
row and column from worksheet1 together...example below worksheet 1 COLUMM ROW.. MILK VEGETABLES FRUITS MEATS salesperson1 ?? ?? ?? ?? salesperson2 ?? ?? ?? ?? salesperson3 ?? ?? ?? ?? salesperson4 ?? ?? ?? ?? worksheet2 ( sales report) ROW 1 ROW2 ROW3 salesperson1 MILK 321 salesperson1 CHOCOLATE 211 salesperson1 MEATS 765 salesperson2 VEGATABLES 90 salesperson2 DONUTS 672 salesperson3 FRUITS 389 salesperson3 SALADS 300 salesperson4 MILK 111 salesperson4 FRUITS 865 -- Listu |
Lookup or Match with rows and column together
Hi
I am assuming that sheet2 you meant the data is in Columns A to C and not Rows 1 to 3 On sheet1 in cell B2 =IF($A2="","",INDEX(Sheet2!$A:$C,MATCH($A2,Sheet2! $A:$A,0),MATCH(B$2,Sheet2!$1:$1,0))) Copy across through cells C2:E2 Copy B2:E2 down as far as required Better still create a Pivot Table of the data on Sheet2 Insert a Header row at Row1 with Name, Product and Value as the column Headings Place cursor in cell A1DataPivot TableFinish On the PT skeleton that appears on a new sheet, Drag Name to the Row area Drag Product to the Column area Drag Value to the Data area as Sum of Value The PT instructions are for XL2003. Post back if you want instruction for XL2007 -- Regards Roger Govier "Listu" wrote in message ... i would like to look up a value from worksheet2 based on cells matching the row and column from worksheet1 together...example below worksheet 1 COLUMM ROW.. MILK VEGETABLES FRUITS MEATS salesperson1 ?? ?? ?? ?? salesperson2 ?? ?? ?? ?? salesperson3 ?? ?? ?? ?? salesperson4 ?? ?? ?? ?? worksheet2 ( sales report) ROW 1 ROW2 ROW3 salesperson1 MILK 321 salesperson1 CHOCOLATE 211 salesperson1 MEATS 765 salesperson2 VEGATABLES 90 salesperson2 DONUTS 672 salesperson3 FRUITS 389 salesperson3 SALADS 300 salesperson4 MILK 111 salesperson4 FRUITS 865 -- Listu __________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________ The message was checked by ESET Smart Security. http://www.eset.com |
Lookup or Match with rows and column together
Hi Roger,
okay, i first tried the piviot table as you suggested. (see below) Yes, i may need some instructions. It seemed to have worked except why did the values all become 1 instead of the actual values i had. Once i get this piviot table which is extremely fast, will the formula change? This pt will be from a sales report that list all products (i changed the product to beverages) although im only tracking specific products from this sales report. That is why i had sheet1 which was my actual spreadsheet with tracking of the products that i want. Hope this makes sense..Thank you. Listu Value (All) Count of Value Column Labels Row Labels dew mist orge pepsi straw (blank) Grand Total Salesp1 1 1 Salesp2 1 1 Salesp3 1 1 Salesp4 Salesp5 1 (blank) Grand Total 1 1 1 1 1 5 -- Listu "Roger Govier" wrote: Hi I am assuming that sheet2 you meant the data is in Columns A to C and not Rows 1 to 3 On sheet1 in cell B2 =IF($A2="","",INDEX(Sheet2!$A:$C,MATCH($A2,Sheet2! $A:$A,0),MATCH(B$2,Sheet2!$1:$1,0))) Copy across through cells C2:E2 Copy B2:E2 down as far as required Better still create a Pivot Table of the data on Sheet2 Insert a Header row at Row1 with Name, Product and Value as the column Headings Place cursor in cell A1DataPivot TableFinish On the PT skeleton that appears on a new sheet, Drag Name to the Row area Drag Product to the Column area Drag Value to the Data area as Sum of Value The PT instructions are for XL2003. Post back if you want instruction for XL2007 -- Regards Roger Govier "Listu" wrote in message ... i would like to look up a value from worksheet2 based on cells matching the row and column from worksheet1 together...example below worksheet 1 COLUMM ROW.. MILK VEGETABLES FRUITS MEATS salesperson1 ?? ?? ?? ?? salesperson2 ?? ?? ?? ?? salesperson3 ?? ?? ?? ?? salesperson4 ?? ?? ?? ?? worksheet2 ( sales report) ROW 1 ROW2 ROW3 salesperson1 MILK 321 salesperson1 CHOCOLATE 211 salesperson1 MEATS 765 salesperson2 VEGATABLES 90 salesperson2 DONUTS 672 salesperson3 FRUITS 389 salesperson3 SALADS 300 salesperson4 MILK 111 salesperson4 FRUITS 865 -- Listu __________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________ The message was checked by ESET Smart Security. http://www.eset.com |
Lookup or Match with rows and column together
Hi
Right click on the PTPT WizardLayoutDouble click your field in the data area and choose Sum instead of Count. For more help on Pivot tables take a look here http://www.contextures.com/tiptech.html and scroll to the section on Pivot tables and here http://www.datapigtechnologies.com/ExcelMain.htm -- Regards Roger Govier "Listu" wrote in message ... Hi Roger, okay, i first tried the piviot table as you suggested. (see below) Yes, i may need some instructions. It seemed to have worked except why did the values all become 1 instead of the actual values i had. Once i get this piviot table which is extremely fast, will the formula change? This pt will be from a sales report that list all products (i changed the product to beverages) although im only tracking specific products from this sales report. That is why i had sheet1 which was my actual spreadsheet with tracking of the products that i want. Hope this makes sense..Thank you. Listu Value (All) Count of Value Column Labels Row Labels dew mist orge pepsi straw (blank) Grand Total Salesp1 1 1 Salesp2 1 1 Salesp3 1 1 Salesp4 Salesp5 1 (blank) Grand Total 1 1 1 1 1 5 -- Listu "Roger Govier" wrote: Hi I am assuming that sheet2 you meant the data is in Columns A to C and not Rows 1 to 3 On sheet1 in cell B2 =IF($A2="","",INDEX(Sheet2!$A:$C,MATCH($A2,Sheet2! $A:$A,0),MATCH(B$2,Sheet2!$1:$1,0))) Copy across through cells C2:E2 Copy B2:E2 down as far as required Better still create a Pivot Table of the data on Sheet2 Insert a Header row at Row1 with Name, Product and Value as the column Headings Place cursor in cell A1DataPivot TableFinish On the PT skeleton that appears on a new sheet, Drag Name to the Row area Drag Product to the Column area Drag Value to the Data area as Sum of Value The PT instructions are for XL2003. Post back if you want instruction for XL2007 -- Regards Roger Govier "Listu" wrote in message ... i would like to look up a value from worksheet2 based on cells matching the row and column from worksheet1 together...example below worksheet 1 COLUMM ROW.. MILK VEGETABLES FRUITS MEATS salesperson1 ?? ?? ?? ?? salesperson2 ?? ?? ?? ?? salesperson3 ?? ?? ?? ?? salesperson4 ?? ?? ?? ?? worksheet2 ( sales report) ROW 1 ROW2 ROW3 salesperson1 MILK 321 salesperson1 CHOCOLATE 211 salesperson1 MEATS 765 salesperson2 VEGATABLES 90 salesperson2 DONUTS 672 salesperson3 FRUITS 389 salesperson3 SALADS 300 salesperson4 MILK 111 salesperson4 FRUITS 865 -- Listu __________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________ The message was checked by ESET Smart Security. http://www.eset.com |
Lookup or Match with rows and column together
Hi,
I got the pivot table, thank you. I went through the tutorials in datapigtechnologies... excel. My formula still didnt come out. I now want to look for the exact value match from sheet2 for the specific products in sheet1, mist pepsi and straw only from my ptable. i did a =INDEX(the whole pivot table,MATCH(Salesman1 in sheet1,the whole pt),MATCH(product..ex mist in sheet1,the whole pivot table) it still does not work. Thanks mist pepsi straw Salesman1 #N/A Salesman2 Salesman3 Salesman4 Salesman5 Sum of Value Column Labels Row Labels dew mist orge pepsi straw (blank) Grand Total Salesman1 23 Salesman2 5 Salesman3 14 Salesman4 87 Salesman5 33 Grand Total 87 23 33 14 5 162 -- Listu "Roger Govier" wrote: Hi Right click on the PTPT WizardLayoutDouble click your field in the data area and choose Sum instead of Count. For more help on Pivot tables take a look here http://www.contextures.com/tiptech.html and scroll to the section on Pivot tables and here http://www.datapigtechnologies.com/ExcelMain.htm -- Regards Roger Govier "Listu" wrote in message ... Hi Roger, okay, i first tried the piviot table as you suggested. (see below) Yes, i may need some instructions. It seemed to have worked except why did the values all become 1 instead of the actual values i had. Once i get this piviot table which is extremely fast, will the formula change? This pt will be from a sales report that list all products (i changed the product to beverages) although im only tracking specific products from this sales report. That is why i had sheet1 which was my actual spreadsheet with tracking of the products that i want. Hope this makes sense..Thank you. Listu Value (All) Count of Value Column Labels Row Labels dew mist orge pepsi straw (blank) Grand Total Salesp1 1 1 Salesp2 1 1 Salesp3 1 1 Salesp4 Salesp5 1 (blank) Grand Total 1 1 1 1 1 5 -- Listu "Roger Govier" wrote: Hi I am assuming that sheet2 you meant the data is in Columns A to C and not Rows 1 to 3 On sheet1 in cell B2 =IF($A2="","",INDEX(Sheet2!$A:$C,MATCH($A2,Sheet2! $A:$A,0),MATCH(B$2,Sheet2!$1:$1,0))) Copy across through cells C2:E2 Copy B2:E2 down as far as required Better still create a Pivot Table of the data on Sheet2 Insert a Header row at Row1 with Name, Product and Value as the column Headings Place cursor in cell A1DataPivot TableFinish On the PT skeleton that appears on a new sheet, Drag Name to the Row area Drag Product to the Column area Drag Value to the Data area as Sum of Value The PT instructions are for XL2003. Post back if you want instruction for XL2007 -- Regards Roger Govier "Listu" wrote in message ... i would like to look up a value from worksheet2 based on cells matching the row and column from worksheet1 together...example below worksheet 1 COLUMM ROW.. MILK VEGETABLES FRUITS MEATS salesperson1 ?? ?? ?? ?? salesperson2 ?? ?? ?? ?? salesperson3 ?? ?? ?? ?? salesperson4 ?? ?? ?? ?? worksheet2 ( sales report) ROW 1 ROW2 ROW3 salesperson1 MILK 321 salesperson1 CHOCOLATE 211 salesperson1 MEATS 765 salesperson2 VEGATABLES 90 salesperson2 DONUTS 672 salesperson3 FRUITS 389 salesperson3 SALADS 300 salesperson4 MILK 111 salesperson4 FRUITS 865 -- Listu __________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________ The message was checked by ESET Smart Security. http://www.eset.com |
Lookup or Match with rows and column together
Hi
The PT gives you the results for everybody on the PT report sheet. You can just filter the Names and / or Products for just the people and products you want. there is no need to apply any formulae. If you are just wanting to extract to your Sheet2, only the items you mention, then just use the Index formula I gave you in my first post. -- Regards Roger Govier "Listu" wrote in message ... Hi, I got the pivot table, thank you. I went through the tutorials in datapigtechnologies... excel. My formula still didnt come out. I now want to look for the exact value match from sheet2 for the specific products in sheet1, mist pepsi and straw only from my ptable. i did a =INDEX(the whole pivot table,MATCH(Salesman1 in sheet1,the whole pt),MATCH(product..ex mist in sheet1,the whole pivot table) it still does not work. Thanks mist pepsi straw Salesman1 #N/A Salesman2 Salesman3 Salesman4 Salesman5 Sum of Value Column Labels Row Labels dew mist orge pepsi straw (blank) Grand Total Salesman1 23 Salesman2 5 Salesman3 14 Salesman4 87 Salesman5 33 Grand Total 87 23 33 14 5 162 -- Listu "Roger Govier" wrote: Hi Right click on the PTPT WizardLayoutDouble click your field in the data area and choose Sum instead of Count. For more help on Pivot tables take a look here http://www.contextures.com/tiptech.html and scroll to the section on Pivot tables and here http://www.datapigtechnologies.com/ExcelMain.htm -- Regards Roger Govier "Listu" wrote in message ... Hi Roger, okay, i first tried the piviot table as you suggested. (see below) Yes, i may need some instructions. It seemed to have worked except why did the values all become 1 instead of the actual values i had. Once i get this piviot table which is extremely fast, will the formula change? This pt will be from a sales report that list all products (i changed the product to beverages) although im only tracking specific products from this sales report. That is why i had sheet1 which was my actual spreadsheet with tracking of the products that i want. Hope this makes sense..Thank you. Listu Value (All) Count of Value Column Labels Row Labels dew mist orge pepsi straw (blank) Grand Total Salesp1 1 1 Salesp2 1 1 Salesp3 1 1 Salesp4 Salesp5 1 (blank) Grand Total 1 1 1 1 1 5 -- Listu "Roger Govier" wrote: Hi I am assuming that sheet2 you meant the data is in Columns A to C and not Rows 1 to 3 On sheet1 in cell B2 =IF($A2="","",INDEX(Sheet2!$A:$C,MATCH($A2,Sheet2! $A:$A,0),MATCH(B$2,Sheet2!$1:$1,0))) Copy across through cells C2:E2 Copy B2:E2 down as far as required Better still create a Pivot Table of the data on Sheet2 Insert a Header row at Row1 with Name, Product and Value as the column Headings Place cursor in cell A1DataPivot TableFinish On the PT skeleton that appears on a new sheet, Drag Name to the Row area Drag Product to the Column area Drag Value to the Data area as Sum of Value The PT instructions are for XL2003. Post back if you want instruction for XL2007 -- Regards Roger Govier "Listu" wrote in message ... i would like to look up a value from worksheet2 based on cells matching the row and column from worksheet1 together...example below worksheet 1 COLUMM ROW.. MILK VEGETABLES FRUITS MEATS salesperson1 ?? ?? ?? ?? salesperson2 ?? ?? ?? ?? salesperson3 ?? ?? ?? ?? salesperson4 ?? ?? ?? ?? worksheet2 ( sales report) ROW 1 ROW2 ROW3 salesperson1 MILK 321 salesperson1 CHOCOLATE 211 salesperson1 MEATS 765 salesperson2 VEGATABLES 90 salesperson2 DONUTS 672 salesperson3 FRUITS 389 salesperson3 SALADS 300 salesperson4 MILK 111 salesperson4 FRUITS 865 -- Listu __________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________ The message was checked by ESET Smart Security. http://www.eset.com |
Lookup or Match with rows and column together
|
| All times are GMT +1. The time now is 02:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com