LookUp with multiple criteria
 

My data is in worksheet1 as follows (the headers start in cell A1), with 3
rows for each date of the month:

date code num
2/1/06 B 100
2/1/06 C 200
2/1/06 W 300
2/2/06 B 400
2/2/06 C 500
2/2/06 W 600

On worksheet2 I want to enter a date in cell A1 and then in cell B1 return
the value in the "num" column based on the code(B,C,W) for that date

Thanks for your help

LookUp with multiple criteria
 

Try something like this:

With the scenario you posted....

On Sheet2
A1: 2/2/2006
A2: B
B1:
=SUMPRODUCT((Sheet1!A2:A7=Sheet2!A1)*(Sheet1!B2:B7 =Sheet2!A2)*Sheet1!C2:C7)
In this instance, that formula returns 400.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"mike47338" wrote:

My data is in worksheet1 as follows (the headers start in cell A1), with 3
rows for each date of the month:

date code num
2/1/06 B 100
2/1/06 C 200
2/1/06 W 300
2/2/06 B 400
2/2/06 C 500
2/2/06 W 600

On worksheet2 I want to enter a date in cell A1 and then in cell B1 return
the value in the "num" column based on the code(B,C,W) for that date

Thanks for your help

LookUp with multiple criteria
 

This was exactly what I was looking for. Thanks very much


"Ron Coderre" wrote:

Try something like this:

With the scenario you posted....

On Sheet2
A1: 2/2/2006
A2: B
B1:
=SUMPRODUCT((Sheet1!A2:A7=Sheet2!A1)*(Sheet1!B2:B7 =Sheet2!A2)*Sheet1!C2:C7)
In this instance, that formula returns 400.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"mike47338" wrote:

My data is in worksheet1 as follows (the headers start in cell A1), with 3
rows for each date of the month:

date code num
2/1/06 B 100
2/1/06 C 200
2/1/06 W 300
2/2/06 B 400
2/2/06 C 500
2/2/06 W 600

On worksheet2 I want to enter a date in cell A1 and then in cell B1 return
the value in the "num" column based on the code(B,C,W) for that date

Thanks for your help