Returning values against two criteria

X

xexex

Hi - I am 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
 
T

troy@eXL

Hi - I am 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

Hi Ed,

You can use Vlookup for this after first creating a unique ID for each
security/date combination.

1. In the first sheet insert a column before the security to use as
your key (ID).
2. Put a formula in each of the cells in this column that combines the
security and the date (eg in cell A1 you'd put =B1&C1). Now each
record has a unique identifier to look up.
3. In the 2nd sheet, in the column after the date put your vlookup
which should look something like this (in cell C2): =VLOOKUP($A
$1&B2,Sheet1$A$1:$D$1000,4,FALSE). This assumes you have the security
code in cell A1 and then the list of dates runnning down column B. You
could put the key (ID) on each row (for example in column A) if you'd
prefer and reference this as the first argument in your vlookup.
Basically it will now search the range in the first sheet looking for
a security & date match in the column you just created.
4. Note that it'll return #N/A if there is no match (eg if you didn't
deal in that security on that day). If you want it to return a blank
cell instead, use ISNA as follows: =IF(ISNA(VLOOKUP($A$1&B2,Sheet1$A
$1:$D$1000,4,FALSE)),"",VLOOKUP($A$1&B2,Sheet1$A$1:$D$1000,4,FALSE))

HTH

Let us know how you go or if you need clarification on any of this.

Cheers,
Troy.

Unprotect Any Spreadsheet... Without The Password... In Just Seconds
Get eXL_unProtect today for less than you'd pay for lunch
www.eXtreme-eXcel.com
Don't Let Anyone Lock You Out Of A Spreadsheet Again!
 
E

excel-ant

Hiya,

You just need to stack some fields to create an index and use VLOOKUP

First insert a new column to the left of the price. (You can later
hide this formula)
Enter a formula in this cell against each entry row, for Row 1 in cell
C1, this would be =A1&"/"&B1, result below
Columns:-
A B
C D
TIVO US Equity 23-Jul TIVO US Equity/23-Jul 136
TIVO US Equity 24-Jul TIVO US Equity/23-Jul 135.5

Then in your second sheet where you want the price to appear for that
Security/Date use a VLOOKUP Formula to look in the stack.

I have produced an example spreadsheet for you to view the exact use
of formula.
I have also made use of the ISERROR function to handle occasions where
the VLOOKUP fails.

http://somethinglikeant.110mb.com//excel-ant/examples/Returning values
against two criteria Options.xls

Hope this helps

Ant
http://www.excel-ant.co.uk
 
X

xexex

Hi Ed,

You can use Vlookup for this after first creating a unique ID for each
security/date combination.

1. In the first sheet insert a column before the security to use as
your key (ID).
2. Put a formula in each of the cells in this column that combines the
security and the date (eg in cell A1 you'd put =B1&C1). Now each
record has a unique identifier to look up.
3. In the 2nd sheet, in the column after the date put your vlookup
which should look something like this (in cell C2): =VLOOKUP($A
$1&B2,Sheet1$A$1:$D$1000,4,FALSE). This assumes you have the security
code in cell A1 and then the list of dates runnning down column B. You
could put the key (ID) on each row (for example in column A) if you'd
prefer and reference this as the first argument in your vlookup.
Basically it will now search the range in the first sheet looking for
a security & date match in the column you just created.
4. Note that it'll return #N/A if there is no match (eg if you didn't
deal in that security on that day). If you want it to return a blank
cell instead, use ISNA as follows: =IF(ISNA(VLOOKUP($A$1&B2,Sheet1$A
$1:$D$1000,4,FALSE)),"",VLOOKUP($A$1&B2,Sheet1$A$1:$D$1000,4,FALSE))

HTH

Let us know how you go or if you need clarification on any of this.

Cheers,
Troy.

Unprotect Any Spreadsheet... Without The Password... In Just Seconds
Get eXL_unProtect today for less than you'd pay for lunchwww.eXtreme-eXcel.com
Don't Let Anyone Lock You Out Of A Spreadsheet Again!- Hide quoted text -

- Show quoted text -

Hey thanks - that is brilliant.

Again, much appreciated, hopefully the stock selection will work just
as well.

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top