Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Why does VLOOKUP work and MATCH function gives #NA 1

Status
Not open for further replies.

wmbb

Technical User
Jul 17, 2005
320
NL
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
 
Can't get your expression to work; please post the actual worksheet
 
@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)]
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
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....
 
Hi wmbb,

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

Link
 
Thank you for the suggestion, i'll look into it [wink]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top