• 544K Members
• 7,777 Online
• 648K Conversations
SOLVED

Highlighted
Contributor

# INDEX & MATCH interrupted by Inconsistent Dates

Hello So I have TWO Issues.

Issue #1: My formula in columns F(Outbound), G(Inbound), and H(Total) is only working properly for "Jennifer" AND only up until there is a interruption of chronological order in DATE from the reference data

Issue #2: Due to the reference data not having dates consistently chronologically listed as soon as my INDEX & MATCH formula hits a date not in order from the reference data it will #N/A

What in SAM HILL TARNATION is going on?!?!?!

18 Replies
Highlighted
Solution

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Hi,

The formula you use isn't correct, and some dates in column N are treated as text not as numeric dates.

Please check out the fix in the attached file.

Regards

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

@shade206Performing a match on two conditions like this with an agent and a date whilst returning a numeric value could be done easier with a SUMIFS such as below.

=SUMIFS(\$O\$2:\$O\$28,\$M\$2:\$M\$28,\$D\$6,\$N\$2:\$N\$28,E9)

Alan

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

@shade206What @Haytham Amairah mentions on some of the dates being text is also true and needs addressing.

My formula will not work for Kelly whilst their dates are not recognised as one.

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Thank you for the Formula Fix!

Follow-up: In this example i have like 30 rows of data, but in my actual spreadsheet i have around 6000... how can i fix ALL the dates to be treated as "Date" or Text" in one swoop without selecting all changing their format and than manually retyping date for 6000 rows? @Haytham Amairah

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

@shade206The dates in this example were stored as text because they were in a MM-DD-YYYY and not a DD-MM-YYYY format.

The easiest way to convert them is to select them and use Text to Column. The final step provides a option to convert dates quickly.

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Please see my replies to this post.

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

What steps did you EXACTLY take to fix the dates in the sample i sent you?

I've been told the solution to my dates problem before and no matter what I do even following yours and Allan's lead right now i'm unable to fix the dates problem aside from individually formatting the cells and than retyping in the correct date which is not feasible for 6000 rows... you have the 30 rows from the sample, what did you to in those 30 rows as i see their Syntax is still the same @Haytham Amairah

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

@shade206I have written an article here which describes different techniques - http://bit.ly/2Hvig1h

For your data, I selected the problematic dates (MM-DD-YYYY ones). Clicked Data > Text to Columns. Bypassed the first two steps as insignificant. On Step 3 I changed the Date option to M-D-Y.

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Thank you sir, that does most definitely work in the sample, but sadly those same steps will not correct the issue in my actual worksheet.... its odd because i deliberately copied the dates from my actual worksheet into the sample... @Alan Murray

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Ok, we would need to know what is causing the text formatting to lead us to a solution. The problem in the sample was the mixture of D-M-Y and M-D-Y formats.

If that is not true on the actual worksheet, then something else is causing it and made need a different response.
Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Check out this link to see how I fix the dates:

If that doesn't help, the reason may be that there are inconsistent dates in your complete data.

If you can provide us with a sufficient sample to see what the problem is.

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Here is a more complete worksheet so you can see what i'm dealing with  @Haytham Amairah

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Here is a more complete worksheet so you can see what i'm dealing with... @Alan Murray

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Hi,

The dates have been fixed with the same steps I showed you.
I guess it didn't work on your side because your Windows date system is in DD/MM/YYYY (UK format) while the dates are created in MM/DD/YYYY (US format).
(Please note that the default dates format in Excel always follow the default dates format in the operating system)

The cause of the problem is that some of them have hidden characters that make Excel read them as texts.
So when you open the workbook, the numeric dates will show up as DD/MM/YYYY whereas the text dates will stay in MM/DD/YYYY which make a mix dates.

In my Windows, I have the date system in US format, therefore the solution works in my side.

Find the solution in the attached file.

Hope that helps
Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

@shade206Yes, you have some inconsistent dates. Some UK format and others in US. I am using a UK system format. So mine recognises the D-M-Y an stores anything else as text.

In the attached file, I used a formula in the last column to check if the date was seen as numeric or text. Then I filtered the list t only show the non-numerics.

Then I selected those, used Go To Special to be sure that visible cells only was selected. And did the Text to Columns approach mention on previous replies.

You have some dates that do not appear in the source producing #N/A errors. You can use the IFNA or IFERROR formula to replace these with something more useful.

I hope this helps.

Alan

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Thank you SO MUCH! its now working as needed!
Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Thank you SO MUCH! its now working as needed!@Alan Murray

Highlighted

# Re: INDEX & MATCH interrupted by Inconsistent Dates

Excellent!
Related Conversations
Date filtering of Bing Searches
PhilipE3 in Discussions on
2 Replies
Setting Date in Stored Procedure
Tim Hunter in SQL Server on
1 Replies
Matching Excel cells by column
ddelise in Excel on
3 Replies
Date Calculator / Seasonal rates
Brian Thornton in Excel on
2 Replies