Lookup & Index Match - Errors Returned

Hi Guys,

I am trying to set up a formula that checks a date is between 2 dates in a table and returns the corresponding column.

A	B	C
Start Date	End Date	Period
28/02/2009	27/02/2010	2009/10

The problem I am having is that if I search for 28/02/2009 it returns #N/A, this also occurs when I type 01/03/2009.

The dates in my table a populated by formula if that makes any diferrence and I attached an example for ease of reference.

Many Thanks

Your "date" in C2 is not really a date - it is a text expression. Use this instead:

=DATE(YEAR(MIN(Data_Date)),MONTH(A2),DAY(A2))

then the INDEX/MATCH in B12 will show the result.

Hope this helps.

Pete

In C2 you have a text (it only looks as a date).
Adjust the formula there to make sure it is a value not text:

Please Login or Register  to view this content.

and shall work fine

Thanks for everybody's input.

I don't think I have ever seen as many responses that were near enough identicle!

Well, if your question has been answered, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to spread a bit of Christmas joy around to those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete