Issue with using MATCH to find date earlier than specific date in table

[Sprucie]

I am using the following formula in excel and it is returning an N/A error when I try to use it - I have tried to find out the solution to why this is happening but I have no ideas anymore!

The formula is:

=INDEX(Table3[DATE],MATCH(J12,Table3[DATE],1))

The Table3 DATE column has dates ranging from 04/10/16 back to 12/09/16, and the date within J12 is 26/09/16. I believe my formula should be looking for a date earlier than 26/09/16 within the DATE column, which as stated above there are, but instead it returns with an N/A error. Can you please point me to the issue?

Many thanks in advance,

Sprucie

 

[JoeMo]

Are the dates in your date column real dates (numbers) or text? If real dates, are they arranged in ascending order as required by the type of MATCH (3rd argument is 1) you are using?

 

[Sprucie]

Are the dates in your date column real dates (numbers) or text? If real dates, are they arranged in ascending order as required by the type of MATCH (3rd argument is 1) you are using?

They are real dates (if I change the format to General they appear in a 5-digit format) and the dates are with the earliest at the top - is that correct?

Thanks,

Sprucie

 

[JoeMo]

They are real dates (if I change the format to General they appear in a 5-digit format) and the dates are with the earliest at the top - is that correct?

Thanks,

Sprucie

If you put this formula into an empty cell, what does it return?

=MATCH(J12,Table3[DATE],1)

 

[Sprucie]

If you put this formula into an empty cell, what does it return?

=MATCH(J12,Table3[DATE],1)

Just N/A.

If I set the last argument to -1 it returns a cell for the 26th of September - which is equal to the date in J12, so shouldn't be shown

 

[JoeMo]

Just N/A.

If I set the last argument to -1 it returns a cell for the 26th of September - which is equal to the date in J12, so shouldn't be shown

If your dates are in ascending order, 1 is correct (as you had it initially). The only potential culprit left is Table3. Confirm that this is a true Table: if you click in a cell of the table, do you see table tools on the ribbon menu? If yes, click on table tools and confirm that the table name is Table3. Also confirm that the table has a column heading "Date" (w/o the quote marks. If yes, I see no reason why your formula returns #N/A.

 

[Sprucie]

If your dates are in ascending order, 1 is correct (as you had it initially). The only potential culprit left is Table3. Confirm that this is a true Table: if you click in a cell of the table, do you see table tools on the ribbon menu? If yes, click on table tools and confirm that the table name is Table3. Also confirm that the table has a column heading "Date" (w/o the quote marks. If yes, I see no reason why your formula returns #N/A.

I get the table reference by clicking on the top left hand corner of the table itself, so it definitely exists! The same with the heading - I click at the top of the heading and it auto-fills the reference. Is there anything else I can do to test?

 

[JoeMo]

I get the table reference by clicking on the top left hand corner of the table itself, so it definitely exists! The same with the heading - I click at the top of the heading and it auto-fills the reference. Is there anything else I can do to test?

Only other thing I can think of is that your formula correctly returns #N/A for some value in J12, then you change J12 to a value you expect will not generate #N/A, but you inadvertently have calculation set to Manual so the value remains #N/A. In that case if you then press the F9 key to force calculation, #N/A should be replaced by some date from your table. Obviously, the fix would be to set Calculation to automatic.

 

[Sprucie]

Only other thing I can think of is that your formula correctly returns #N/A for some value in J12, then you change J12 to a value you expect will not generate #N/A, but you inadvertently have calculation set to Manual so the value remains #N/A. In that case if you then press the F9 key to force calculation, #N/A should be replaced by some date from your table. Obviously, the fix would be to set Calculation to automatic.

Unfortunately that hasn't worked either, I believe that it is on Automatic calculation (pressing F9 did also look like it refreshed the data but the result was still #N/A)

I've taken a couple of screenshots which show the different elements: Excel Issue - Album on Imgur

It seems like I must have missed something simple!

 

[JoeMo]

Unfortunately that hasn't worked either, I believe that it is on Automatic calculation (pressing F9 did also look like it refreshed the data but the result was still #N/A)

I've taken a couple of screenshots which show the different elements: Excel Issue - Album on Imgur

It seems like I must have missed something simple!

From the date column image it appears to me that your dates are not arranged in ascending order (oldest date to newest date). They seem to be in descending order. Try sorting them ascending - that's what the form of MATCH you are using requires.