Cells display the same value, but Index Matching does not work

[onionbagels]

Hello,


I am trying to index/match a set of dates on one worksheet with a values along a set of dates in another worksheet (the logic being to pull the value that corresponds to a certain date from one worksheet to another). However I noticed something weird. I am positive that I have all the references keyed in correctly, and it is still saying that the dates don't match. I tried doing a logic test by setting the two cells equal to each other and verifying that it was true, and the test returned false. THEN, I retyped the date in one of the worksheets and it adjusted to true. I made sure that the formatting of both sets of dates were set to date, so I'm unsure why it was returning false beforehand.


Is there a quick way to fix this problem? I have 10 years worth of daily cells so fixing it manually would be impractical? Thank you for your help!

 

[Mike1138]

Make sure the lookup value and the range containing the dates are the same format. If one side or the other is stored as text, the index and match will fail.

 

[onionbagels]

Make sure the lookup value and the range containing the dates are the same format. If one side or the other is stored as text, the index and match will fail.

what if the lookup value itself is not actually a date? do i have to make everything text based?

 

[Mike1138]

what if the lookup value itself is not actually a date? do i have to make everything text based?

Yes. Or it needs to be date based. As long as the lookup field and the lookup value have the same format, your formula should work.

EDITED: For poor grammar.

 

[onionbagels]

Yes. Or it needs to be date based. As long as the lookup field and the lookup value have the same format, your formula should work.

EDITED: For poor grammar.

Thank you, Mike. Just to recap, I will have to format the lookup value cells (currently a non-date value) into a date format to match the lookup field, or adjust the index/match syntax I currently have to make the dates into text (using the text formula?). Is that correct?

Just out of curiosity, do you know why the lookup worked when I manually retyped the date into the same cell?

 

[Mike1138]

Correct on both counts. And your text formula should read TEXT(date,"m/dd/yyyy").

The reason your formula worked after you retyped the date has to do with how Excel stores and displays dates. Excel stores dates as Julian dates (they are sequential integers for each day starting with Jan 1, 1901). So, Jan 1, 1901 is actually stored as 1, and then Excel displays it has the date you're familiar with.

As an experiment, open a blank workbook and type today's date into A1. Select the cell, and change the format from Date to General, and the number will change to 41502.

When you retyped the date, Excel takes the "8/16/2013" that you key in, and converts it to 41502 without ever letting you know it's doing it.

If you type a date in and look at the formula bar, you see it in the format it's displayed in. As far as I know, the Date format is the only format Excel does this with. If you format a number as currency, you don't see a dollar sign in the formula bar. Maybe someone with insider knowledge can explain why Microsoft treated the formats differently.