Match Function

[oakman]

I was wandering if I could ask for some help with a formula. I have 2 sheets. Sheet 1 has raw data for the month which I have subtotaled using the Data menu. Sheet 2 has the only 4 fields of data that I need. These are Date, Product Name, Volume, and Time. I have 14 products and I wanted to get the total volume for each product per day. I have set up a Match formula in sheet 2 that looks at the volume, looks at each product name, and the date. So far, I have not been able to pull the correct total from the set of data on sheet 1. Any help is greatly appreciated. My sheets are set up the following way: sheet 1 – column C (product name SubTotal), column D (day of the month), column K (volume)
Sheet 2 – column B (day of the month), column C4:C17 (product name Total), column D2 (volume)
My match formula is as follows: =OFFSET(Sheet2!$D$2,MATCH(C4,'Sheet 1'!C:C,0),MATCH($B$3,'Sheet 1'!D:D,0))
Thank you!

[Toppers]

Take a look at SUMPRODUCT which I think will better meet your need.

A very full explanation is given here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH

"oakman" wrote:

>
> I was wandering if I could ask for some help with a formula. I have 2
> sheets. Sheet 1 has raw data for the month which I have subtotaled
> using the Data menu. Sheet 2 has the only 4 fields of data that I need.
> These are Date, Product Name, Volume, and Time. I have 14 products and
> I wanted to get the total volume for each product per day. I have set
> up a Match formula in sheet 2 that looks at the volume, looks at each
> product name, and the date. So far, I have not been able to pull the
> correct total from the set of data on sheet 1. Any help is greatly
> appreciated. My sheets are set up the following way: sheet 1 – column
> C (product name SubTotal), column D (day of the month), column K
> (volume)
> Sheet 2 – column B (day of the month), column C4:C17 (product name
> Total), column D2 (volume)
> My match formula is as follows: =OFFSET(Sheet2!$D$2,MATCH(C4,'Sheet
> 1'!C:C,0),MATCH($B$3,'Sheet 1'!D:D,0))
> Thank you!
>
>
> --
> oakman
> ------------------------------------------------------------------------
> oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172
> View this thread: http://www.excelforum.com/showthread...hreadid=564368
>
>

[Toppers]

Take a look at SUMPRODUCT which I think will better meet your need.

A very full explanation is given here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH

"oakman" wrote:

>
> I was wandering if I could ask for some help with a formula. I have 2
> sheets. Sheet 1 has raw data for the month which I have subtotaled
> using the Data menu. Sheet 2 has the only 4 fields of data that I need.
> These are Date, Product Name, Volume, and Time. I have 14 products and
> I wanted to get the total volume for each product per day. I have set
> up a Match formula in sheet 2 that looks at the volume, looks at each
> product name, and the date. So far, I have not been able to pull the
> correct total from the set of data on sheet 1. Any help is greatly
> appreciated. My sheets are set up the following way: sheet 1 – column
> C (product name SubTotal), column D (day of the month), column K
> (volume)
> Sheet 2 – column B (day of the month), column C4:C17 (product name
> Total), column D2 (volume)
> My match formula is as follows: =OFFSET(Sheet2!$D$2,MATCH(C4,'Sheet
> 1'!C:C,0),MATCH($B$3,'Sheet 1'!D:D,0))
> Thank you!
>
>
> --
> oakman
> ------------------------------------------------------------------------
> oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172
> View this thread: http://www.excelforum.com/showthread...hreadid=564368
>
>

[oakman]

Thank you for the guidance HTH.
The paper looks very complete and suitable.