Formula not finding start and end dates for subsequent months in range

Attached file Datespan.xlsx lists months and Start and End Dates in Cols AI - AK.

User sets "First month" through Data Validation in A3.

"Index/Match" in A2 and B2 identify correctly the Start and end dates for that first month

Cols C - W then show subsequent eleven months using Formula =EOMONTH(A3,"whatever")

But Index/Match formula does not show the Start and End dates for them.

Any suggestions and solutions received gratefully

Ochimus

I offer this option.

Hi Robert,

C2: =INDEX($AI:$AI,MATCH(C3,$AI$1:$AI$26,0)) and MATCH(C3,$AI$1:$AI$26,0) looking for the exact value of C3 in $AI$1:$AI$26 range. It can't be done because there is no value = C3.
Excel stores dates as sequential serial numbers so they can be used in calculations.
See: If A3 = Jan-15 and next C3 = Feb-15 but in real this is a number 42063 (28/02/2015).
In the same time in $AI$1:$AI$26 range you have date: Feb-15 (cell AI6) what is a number: 42036 (01/02/2015).
Looks the same (Feb-15) but they are different (42063 <> 42036 | 28/02/2015 <> 01/02/2015) so you have an error: #N/A, because C3 doesn't equal AI6. Btw, I think this is not error but info: Not Available
Excel working with numbers but not with a visual representation of the date.

In this case, I recommend to use =EDATE(A3,1) instead of =EOMONTH(A3,1) because EOMONTH() returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month, but EDATE() returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE() to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

btw, imho C2 should be: =INDEX($AI:$AK,MATCH(C3,$AI$1:$AI$26,0),2), D2: =INDEX($AI:$AK,MATCH(C3,$AI$1:$AI$26,0),3) if headers have to be consistent, but it's up to you

I offer this option.

look at post time

Many thanks to everyone who "chipped in", and especially for the download.

Can't believe I was daft enough to use different days in Row 3 to Column AI!

And Sandy is quite correct that headers should have been consistent - as my grandchildren will probably be asked by the User in twenty years' time how I achieved the end result.

Ochimus

You are welcome and thanks for rep