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

 

[excelos]

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)

Thanks but what is --('Date Tracker'!$E$5:$DT$5)=J6 ?

 

[steve the fish]

As your dates are headers in a table they will be seen as text. The double negative will coerce the text dates into real dates. They can then be compared to J6 to see if they are equal. This will produce an array of TRUE/FALSE values.

 

[Aladin Akyurek]

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.

This match formula as is does not require control+shift+enter.

In case 'Date Tracker'!$E$5:$DT$5 consists of text numbers: Control+shift+enter, not just enter...

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

Does this succeed?

 

[excelos]

This match formula as is does not require control+shift+enter.

In case 'Date Tracker'!$E$5:$DT$5 consists of text numbers: Control+shift+enter, not just enter...

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

Does this succeed?

Nope, it doesn't work

 

[Aladin Akyurek]

Nope, it doesn't work

What does it do, return #N/A or a wrong result?

Care to post E5:K5 from Date Tracker'!$E$5:$DT$5?

 

[excelos]

What does it do, return #N/A or a wrong result?

Care to post E5:K5 from Date Tracker'!$E$5:$DT$5?

Ah it works when inserted as array formula!
Btw, you do +0 to convert it into text? Or how exactly does it work?

Thanks

 

[Aladin Akyurek]

Ah it works when inserted as array formula!
Btw, you do +0 to convert it into text? Or how exactly does it work?

Thanks

+0 converts the text dates into true dates, i.e., numbers. Since J6 is a date/number, MATCH succeeds.