formula that searches an array

On the morning of the Ex-Dividend Date, dividend stocks typically fall from the price the day before the Ex-dividend date by the amount of the declared dividend. The stock price then recovers back to the price (equals or exceeds) on the day before the Ex-Dividend Date, where that may take a day or many days but typically only a few days.

I need a formula that reviews a table of historical prices and supplies (in cells T5 and T6 inside the Thick Box Borders) the number of days it takes for the price to recover. I need for the formula to also supply that number from a specific ticker symbol, where the prices are organized by ticker symbol.

The formula I have (in cell T5) uses AGGREGATE, INDEX, MATCH, and ROW. It performs the first part (Days To Recover) but not the second part (specific to a Ticker), and thus does not work for a large table with many prices for many different Tickers.

See workbook attached.

I need help fixing that formula.

Thanks for your help!

Bebo – your formulas work on the worksheet I uploaded. Thank You!

I do not completely understand your formula for “U5, recovery date”, and if I did I probably would not need to post my question to the Forum in the first place.

With that said, I think it will work when I apply it to a much larger worksheet, but I will need to double-check that when I do. If there is a problem, I will repost.

Thanks again!

Will do. Thanks!

bebo - I understand your formulas in S5: Buy price and T5: day count. The part of your "U5: recovery date" formula I don't understand is MATCH(TRUE,INDEX($L$3:$L$33/($A$3:$A$33=$N5)/($B$3:$B$33>$P5)>S5,)

I understand the $L$3:$L$33 and the ($A$3:$A$33=$N5) and the ($B$3:$B$33>$P5)>S5; however, I don't understand using the "/" symbol. Can you please explain what the "/" symbol does?

Thanks!

bebo - Thanks so much for the lesson in array logic and the associated syntax.

I understand what you are saying conceptually. However, I need to sleep on it and think it through a bit more. Very helpful.

Thanks again!