Forum Discussion
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?!?!?!
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
18 Replies
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
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.
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
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
