Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced lookup or match?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced lookup or match?
Give this a try:
=SUMPRODUCT(--(Sheet1!$A$1:$A$20=A1),--(Sheet1!$B$1:$B$20=B1),Sheet1!$C$1:$C$20) "xexex" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Excel: offset, index/match, lookup, other? help!! | Excel Worksheet Functions | |||
advanced lookup/index-match problem | Excel Worksheet Functions | |||
advanced lookup/index-match problem | Excel Discussion (Misc queries) | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions |