Looking up the Closest Date less 45 days

[Kapital]

Hello everyone,

I have an excel problem for those clever enough to solve it. It has become quite a headache so I appreciate any insight in advance. I will keep this simple, but in reality my database is quite large so I need an efficient formula.

Spreadsheet 1 (three columns titled ID, Date, Value)

ID Date Value
ABC 11-Sept-2012 ?


Spreadsheet 2

ID Date Value
ABC 19/07/2012 2
ABC 10/10/2012 5


Okay so I need to do a vlookup (or some formula of the kind) of the Security ABC in Spreadsheet 1 against that in Spreadsheet 2. However I need the date (11-Sept-2012) to detect the 19/07/2012 entry and not 10/10/2012 (which is closest). Notice the difference between the two dates in Spreadsheet 2 is almost 3 months. So there are 90 days approx in this bloc, 45 days is the midpoint. What I originally tried was a vlookup of Spreadsheet 1, but deducting 45 days from 11-Sept-2012 which would give me 28-Jul-12 and hence return a value of 2. But unfortunately I couldn't get this to work or formulate it correctly, but I know this logic works.

Anyone know how to formulate this WITHOUT using macros/vba code?

[kvsrinivasamurthy]

Try this in C2 of Spreadsheet1.
Assumed that dates are in B column B2:B10.


=index('Spreadsheet 2'!B2:B10,match(B2,'Spreadsheet 2'!B2:B10)+1)

[Fotis1991]

Hi

Assuming that your data starts in A1 of Sheet1.

Then in D1(This will be helper and hidden column), put this and copy down.

=IF(B1="","";IF(B1>0,Sheet3!$B$1-B1)-45)

In Sheet 2, an A1,is ABC, In B1=11/09/12.

In C1, put this Array formula. Confirm with Control+Shift+Enter. Not just Enter.

=INDEX(Sheet1!$C$1:$C$10,MATCH(SMALL(IF(Sheet1!$D$1:$D$10>0,Sheet1!$D$1:$D$10),1),Sheet1!$D$1:$D$10,0))

Is this, OK for you?

[Kapital]

Sorry kvsrinivasamurthy, didn't work.

Fotis1991, thanks for you reply also. I tried to implement it but it failed for me. I have attached the spreadsheet so maybe you can have a crack at giving it a go when you have the time.

Thankyou all again.

[kvsrinivasamurthy]

Pl see the attached file. Formula in C2 cell of Sheet1.

[Kapital]

Thanks mate for your response. However I probably didn't make myself clear, I want to detect the value for that date (that is 19/07/2012), which is 2 in my example to return in the value field for Spreadsheet 1(i.e. replace ? with 2). I know its probably one extra step, but any ideas how to simplify the formula into one step?

Thanks again

[daddylonglegs]

The first range dictates the column from which the value is returned so this should return 2 for your example

=INDEX(Sheet2!C$1:C$10,MATCH(B1,Sheet2!B$1:B$10))

.....but that doesn't match "ABC", I assume you want that matched too? Will dates all be in ascending order within each security? If so try this formula

=LOOKUP(2,1/(B1>=Sheet2!B$1:B$10)/(A1=Sheet2!A$1:A$10),Sheet2!C$1:C$10)

[Kapital]

@ daddylonglegs


Thanks, it seems to have worked. Will try it on a much larger scale tomorrow. Could you please explain this formula so I have an understanding of what it is doing: =LOOKUP(2,1/(B1>=Sheet2!B$1:B$10)/(A1=Sheet2!A$1:A$10),Sheet2!C$1:C$10)