XML Data and Match/Index Formula

[Skiracer65]

I have an XML file that I have imported into my worksheet with several columns of data. I am wanting to search out values based upon entering a date and displaying the data in a corresponding row. I have no problem writing the index/match formula to look up the data, yet have been running in a "N/A" error. Here's what happens. When I enter the date to be referenced, the formula returns the error. Yet if I then go to data page, delete the date and then enter the same date in manually, the formula returns the desired data. I've messed around with formatting and clearing all formats, yet still run into the same problem. I know my fix is easy to just go to the data and re-enter it all manually, but I have over 3k rows of dates so not really a good use of time. Does anyone know how I can fix this?

Thank you,
Nathan

[DonkeyOte]

Can you post a small sample ?

It sounds as though your dates are date strings rather than true date values (numbers) ... when you re-enter them Excel re-interprets the entry accordingly.

Your choices are either to

a) update the source values such that the strings become numeric (ie coerce the strings)

b) adjust your criteria such that you look for a date string rather than a date (number) - ie match your source values

Option a) would generally be advised - how you can achieve this will largely depend on your data (which we can't see) but you should find you can update all simultaneously.

On a final note:

I've messed around with formatting and clearing all formats, yet still run into the same problem

altering the format of a cell post data entry does not alter the data type characteristics of the underlying value.
(in much the same way that typing Apple into A1 and formatting A1 as Number does not make Apple numeric)

[Skiracer65]

I have attached the worksheet. I do see some progress when changing the date formats to XX90 and XX00, but still have problems searching out certain dates. The formula is written into worksheet 2.

[Skiracer65]

Also, I have converted previous copies to numbers, and keep running into the same problems. I am not very familiar with XML and am assuming something is written into the source that is giving me the problem.

[DonkeyOte]

Referring back to Option B (ie leaving original source values untouched):

instead of using A1 as your criteria in the MATCH use TEXT(A1,"DD-MMM-YY") as your criteria (or alternatively enter '01-Jan-90 in A1 rather than the date)

In the above you're essentially searching for the String rather than the Date Integer.

If you want to convert originals to dates - highlight column A on Sheet1 and run Data -> Text to Columns -> Delimited -> Step 3 choose Date DMY and click Finish.