Why a MATCH formula may not work?

[excelos]

Hello,

Any idea why a MATCH command does not work? I definitely see the value but it returns N/A.
I have entered it as an array.

Is there a formatting issue?

Thanks

 

[gaz_chops]

Looking for a value that is shown as text.

Some idea of the formula/data you are using may be useful.

 

[Fluff]

Without seeing your data, a few possibilities.
One of the values is text the other is numeric
One of the values has leading/trailing spaces.
If the values are numeric, one might be 1.22 & the other 1.219999 but formatted to appear as 1.22
If any of the values have been copied from another source, you may have hidden characters.

 

[excelos]

Okay, that is extremely strange and I have never come across anything like that ever.

The data is the header of a table and no matter what formatting I choose, it does not change the displayed value (which is a date). Is this a 'feature'?

Any idea?

 

[Indystick]

If you're getting #N/A it's because the MATCH statement can't match your lookup value to its array. E.g., you're looking for the letter "E" in the array "A", "B", "C", "D". How to fix:

If you're doing an exact match (with the third argument as 0), then your lookup value has to be in the array.

If you're doing an approximate, <=, match, your array has to be in ascending order.

If you're doing an approximate, >=, match, your array has to be in descending order.

Based on your second post it sound to me like you are doing an exact match. If you're matching on text, try trimming the value you're trying to match on. Could be extra spaces.

 

[gaz_chops]

Sounds like the Header is date in text format and you are looking to match a date value.

 

[Fluff]

The data is the header of a table and no matter what formatting I choose, it does not change the displayed value (which is a date). Is this a 'feature'?

I believe that table headers are converted to text, when the table is created.

 

[Aladin Akyurek]

Try to post the formula you have as asked for.

 

[excelos]

That's the formula:

{=MATCH(J6,'Date Tracker'!$E$5:$DT$5,0)}

I tried to specify a date in J6 and it still does not find it.

I made sure that 'Date Tracker'!$E$5:$DT$5 and J6 are in date format.

 

[steve the fish]

If its a date in a table header than match isnt going to match it. It will see the header as text. Something like this might work:

=MATCH(TRUE,INDEX(--('Date Tracker'!$E$5:$DT$5)=J6,0),0)