INDEX/MATCH for closest value to a certain date

Question

In sheet "Dividends" I have a table with dividends sorted by daily dates. E.g, columns A and B contain the following entries:

6/14/2015    
6/13/2015    
6/12/2015    0.045
6/11/2015
6/10/2015

This means that the stock paid a dividend of 0.045 on 6/12/2015.

In another sheet "AdjClose", I have a table with weekly dates and stock prices, e.g.

6/15/2015   1.23
6/8/2015    1.24
6/1/2015    1.06
5/30/2015   1.10
5/23/2015   1.22
5/16/2015   1.20

I would now compute the yield, where I divide the dividend by the stock price that is closest to the date of the dividend payout, but smaller than that date.

The result should be:

0.045/1.24

How could I do this? Many thanks for any input.

Answer 1

Following 4 named ranges for simplicity in code: "Dividends": DividendDates (column A); DividendsPaid (column B) "AdjClose": StockDate (column A); StockPrice (column B)

try (in column C in "Dividends":

{=INDEX(StockPrice;MATCH(MAX(IF((StockDates<=A1);StockDates));StockDates;0))}

Assuming that the dividend date for which you want to find the adjusted stock price is in cell A1.

And copy down for each dividend date. This will give you the stock price of the day closest to, but before, you dividend date (or on the date if it is the same). Then just devide by your dividend for that day (either in the formula I wrote, or in a separate column. Always good to show your calculations steps, so it is easier to follow.

And remember to press CTRL + SHIFT + ENTER when you enter the formula, not just ENTER, as it is an array formula.

EDIT: Also, you need to change the ; to , if that is the formula separator of your language.

Answer 2

Not the best looking one, but an option:

{=B3/INDEX(AdjClose!$B$1:$B$6,MATCH(MAX((AdjClose!$A$1:$A$6<=Dividends!A3)*AdjClose!$A$1:$A$6),AdjClose!$A$1:$A$6,0))}

Option #2 - much better, as there are no repeating ranges:

{=B3/INDEX(AdjClose!$B$1:$B$6,MATCH(1,--(AdjClose!$A$1:$A$6<=Dividends!A3),0))}