Why does VLOOKUP work and MATCH function gives #NA 1

[wmbb]

I have an excel sheet with a list of dates and I want to find the matching row for a date in another sheet.
The list of dates are formatted as dates, not as text...

Example:
In sheet1 I have a text value in cell A2 like "2022-08-29 09.58.51"
A formula in cell B1 [=VLOOKUP(DATEVALUE(LEFT($A2;10));'sheet2'!$A:$D;2;FALSE)] gives a nice value of 16.9
But when I want to get the row number of the list and put a formula in cell C1 [=MATCH(DATEVALUE(LEFT($A2;10));'sheet2'!$A:$D;0)] I get an error #NA
I've tried a lot of things but I can't figure out what goes wrong...

Can somebody help me in the right direction ?
If the VLOOKUP gives a match then the MATCH function should also do the trick or not.

Thanks in advance

 

[IRstuff]

Can't get your expression to work; please post the actual worksheet

 

[mintjulep]

@IRstuff

See semi-colons as delimiters.

Likely a regional setting.

The use of DATEVALUE() is inconsistent with the claim that "The list of dates are formatted as dates, not as text"

I don't think MATCH likes multiple columns.

[=MATCH(DATEVALUE(LEFT($A2;10));'sheet2'!$A:$D;0)]

 

[SkipVought]

Hi,

VLOOKUP, which I hardly ever use in favor of MATCH & INDEX, references a multi-column range with LOOKUP column in the first column of the referenced range. (It's like a pair of hand-cuffs)

MATCH, on the other hand, references a single column range (that, BTW, can be concatenated) but only single columns.

So just reference column A in your MATCH.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[wmbb]

Hi SkipVought and mintjulep,

Thank you for pointing me in the right direction.
Referencing to just one column did the trick.

I should have found this myself....

 

[remeng]

Hi wmbb,

I'd also recommend getting away from vlookup and move to xlookup. There are a ton of advantages.

Link

 

[wmbb]

Thank you for the suggestion, i'll look into it