MATCH function - dates

Question

am using excel 2007 - have a list of data as follows

column a     -   column b    -    column c

01/01/2010      £100               10/01/2010

01/02/2010      £200               10/02/2010

01/03/2010      £300               10/03/2010

01/04/2010      £400               10/04/2010

01/05/2010      £500               10/05/2010

if i type formula =MATCH("10/03/2010",C1:C5,0) i get a #NA return? no idea why it won't return the position in column c of that date

Answer 1

You are comparing text to a date, instead of a date to a date.

In the formula: =MATCH("10/03/2010",C1:C5,0)

This part: "10/03/2010" is a word...not a date

You'll need to convert it to a date to match it in the list of dates.

Here's one approach:

=MATCH(--"10/03/2010",C1:C5,0)

The double-minus converts the "numeric text" to an actual numeric, a date in this case.

Does that help?

---

Ron Coderre

Microsoft MVP - Excel (2006 - 2010)

P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

Answer 2

Hello,

If your dates in column C are date time constants (like 10/03/2010 12:55:12) but only formatted with dd/mm/yyyy of if your dates are stored as text, for example, this can happen.

Does =MATCH("10/03/2010",INT(C1:C5),0) work?

Or =MATCH(DATE(2010,03,10),INT(C1:C5),0)?

Regards,

Bernd

---

www.sulprobil.com

Answer 3

Hi,

You can do it this way

=MATCH(DATE(2010,3,10),C1:C9)

Answer 4

I like all the solutions in particular Don's because its compact notwithstanding Bernt's correct observation.

An interesting point is if you know that the date you want to match is in the current year you can use any of the following:

=MATCH(--"10/03",C1:C5,0)    (Ron's)

=MATCH(--"10/03",INT(C1:C5),0) array entered (Brent's)  the original would not work because the lookup value was text and because INT is being used against more than one value so you must make it an array.

=MATCH(DATE(2010,10,3),C1:C9) (Mike's)  can't be shortened, but I reversed the month a day arguments.

You could also use

=MATCH(DATEVALUE("10/03"),C1:C5,0)

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 5

many thanks all - all the options seem to work