Advanced lookup or match?

[xexex]

Hi - I am hoping some genius can help me with my excel problem. If you
can, many thanks, in advance.

I have two worksheets: One which has a list of securities, trade
dates, and dealt prices (with blank rows between securities -not shown
below) e.g. (sorry, not real trade prices!)

Security Trade Date Dealt price (domestic)

TIVO US Equity 23-Jul 136
TIVO US Equity 24-Jul 135.5
TIVO US Equity 27-Jul 128.5
TIVO US Equity 13-Aug 126.25
TIVO US Equity 05-Oct 125.25
TIVO US Equity 08-Oct 125.5
BLNX LN Equity 21-Aug 683.5
BLNX LN Equity 31-Aug 704.5
BLNX LN Equity 05-Sep 706
BLNX LN Equity 06-Sep 695
BLNX LN Equity 07-Sep 690
BLNX LN Equity 20-Sep 665
BLNX LN Equity 16-Oct 702
MONY LN Equity 11-Oct 10.3993
MONY LN Equity 12-Oct 10.5
MONY LN Equity 15-Oct 10.28
R8R GR Equity 06-Jul 242.25
R8R GR Equity 13-Jul 243.25
R8R GR Equity 24-Jul 240
R8R GR Equity 03-Aug 225

My second worksheet has security name, and then some dates e.g.

Security wpp ln Equity
Date 02/07/2007
03/07/2007
04/07/2007
05/07/2007
06/07/2007
09/07/2007
10/07/2007
11/07/2007
12/07/2007
13/07/2007
16/07/2007

In the column next to the dates in the second worksheet I want to
return the dealt price from colum three if I dealt in that security on
that day, otherwise I want to leave the cell blank.

Anyone got any ideas? Eternally grateful if so!

Thanks

Ed

 

[Jim May]

Give this a try:

=SUMPRODUCT(--(Sheet1!$A$1:$A$20=A1),--(Sheet1!$B$1:$B$20=B1),Sheet1!$C$1:$C$20)