Index match not working if lookup column contains formulas?

[Filos]

Ι have used, in many circumstances until now, the combination of functions index/match, without a problem.
But this time I faced a very strange problem:
When the lookup column (let's say column B) contains a formula, even the simplest, as “=A1” (in the cell B1), etc., the index(match formula is not working.
When I copy the contents of the lookup column and I paste them in the same column as values, it works perfectly.
There are always ways to surround the problem, but I am very curious to find out what's going on.
I checked, and there does not seem to be a format problem.
I made and posted here a very simplified version in one single sheet, without conditional formatting, data validation etc.
The problem always persist.
I beg anyone who could take a look, to give me an explanation!
I am working with MS Office Professional Plus 2013

[AliGW]

Welcome to the forum.

Your dates in column A and therefore also B are not dates at all - they are just text. That's why you aren't getting the match.

Don't try to fudge this by adapting the formula - tackle the issue in column A and make sure that the values are proper dates.

[sohaila]

Check this file

[Filos]

Sohaila, thank you. I tried it. But it works only for the first row. Please, take a look.

[Filos]

I thank you very much for your prompt answer. It is very strange. I checked it again. The format of the column A is date (type 14/03/12). What am I missing?

[AliGW]

No, that’s not the format. If you change the format to general, if the value is a true date, it will change to a 5-digit serial number. Yours don’t because they are just text. To Excel, they are not the sane thing.

[AliGW]

Check this file

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

[Filos]

Thank you again, AliGW, for your interest about my in my subject. I changed the format to general and, as you say, I took 5-digit serial numbers. I made also a control with the function =CELL, and for all the cells in the column A, the result is D1. Note that I use dates in greek-language format (d/m/y). But I also tried it in english format and the problem persisted.
I attached a renewed version of my workbook with some explanations.
Sorry for my english knowledge. I made perhups, many mistakes...

[Filos]

Finally, I found the solution. It was obvious, but it did not cross my mind.
When the result of the formula in the lookup column (B) is 0, the formula index/match, in the return column, sees the date 0/1/1900 below other dates in ascending order and the formula does not work.
So I replaced the zeros in the lookup column with “”, and now it works perfectly.
Many thanks to all who were interested in my problem.
Excel make us sometimes crazy...

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.