Lookup or Match with rows and column together

L

Listu

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
 
R

Roger Govier

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 A1>Data>Pivot Table>Finish
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 said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4800 (20100123) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
L

Listu

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
 
R

Roger Govier

Hi

Right click on the PT>PT Wizard>Layout>Double 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 said:
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




__________ Information from ESET Smart Security, version of virus
signature database 4802 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
L

Listu

Hi,
I got the pivot table, thank you.
I went through the tutorials in datapigtechnologies...re: 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
 
R

Roger Govier

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 said:
Hi,
I got the pivot table, thank you.
I went through the tutorials in datapigtechnologies...re: 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




__________ Information from ESET Smart Security, version of virus
signature database 4802 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Top