Vlookup based on 2 criteria
 

I have a table that has three values seen below. I would like to
transfer/copy these values into a new worksheet that has the date listed
horizontally on the top and the fund # listed vertically. If it doesn't
return a data it should be 0. No pivot table suggestions please. Please see
below. Thank you in advance

How Data comes in
Fund# Date Amount
2 07/21/08 45
9 07/21/08 36
21 07/18/08 22
61 07/21/08 49
45 07/21/08 45

Result
07/19/08 07/20/08 07/21/08 07/22/08
2 0 0 45
0
9 0 0 49
0
21 0 0 0
0

Vlookup based on 2 criteria
 

Assuming your original data is on a separate tab (I named Data), starting in
cell A1.
On your tab that will have new table, across row 1, starting in column B,
put your dates, down column A, starting in row 2, put your fund number.

The formula in cell B2 is as follows:
=SUMPRODUCT(--(Data!$A$1:$A$5=$A2),--(Data!$B$1:$B$5=B$1),(Data!$C$1:$C$5))

Then copy this formula to all cells that may have a value.
Note: Obviously, if you have more than 5 rows of data on your data tab,k you
will need to expand the above formula accordingly.
--
John C


"Alex" wrote:

I have a table that has three values seen below. I would like to
transfer/copy these values into a new worksheet that has the date listed
horizontally on the top and the fund # listed vertically. If it doesn't
return a data it should be 0. No pivot table suggestions please. Please see
below. Thank you in advance

How Data comes in
Fund# Date Amount
2 07/21/08 45
9 07/21/08 36
21 07/18/08 22
61 07/21/08 49
45 07/21/08 45

Result
07/19/08 07/20/08 07/21/08 07/22/08
2 0 0 45
0
9 0 0 49
0
21 0 0 0
0

Vlookup based on 2 criteria
 

Thank you very much. This worked great!!

"John C" wrote:

Assuming your original data is on a separate tab (I named Data), starting in
cell A1.
On your tab that will have new table, across row 1, starting in column B,
put your dates, down column A, starting in row 2, put your fund number.

The formula in cell B2 is as follows:
=SUMPRODUCT(--(Data!$A$1:$A$5=$A2),--(Data!$B$1:$B$5=B$1),(Data!$C$1:$C$5))

Then copy this formula to all cells that may have a value.
Note: Obviously, if you have more than 5 rows of data on your data tab,k you
will need to expand the above formula accordingly.
--
John C


"Alex" wrote:

I have a table that has three values seen below. I would like to
transfer/copy these values into a new worksheet that has the date listed
horizontally on the top and the fund # listed vertically. If it doesn't
return a data it should be 0. No pivot table suggestions please. Please see
below. Thank you in advance

How Data comes in
Fund# Date Amount
2 07/21/08 45
9 07/21/08 36
21 07/18/08 22
61 07/21/08 49
45 07/21/08 45

Result
07/19/08 07/20/08 07/21/08 07/22/08
2 0 0 45
0
9 0 0 49
0
21 0 0 0
0