VLOOKUP Problem Searching a List of Dates

SidBord

Active Member
Joined
Aug 23, 2004
Messages
346
I have a column of dates that are formatted as:
"(ddd) mmm dd, yy" ........ Looks like "(Sun) Dec 25, 05"

Using the formula:
VLOOKUP(F9, B5:D100,3,FALSE)
where F9 contains "(Sun) Dec 25, 05"
it cannot find the value. I figured the FALSE gets rid of the requirement that the list be in sort order, so why can't VLOOKUP find the date?? I'm convinced it has something to do with the peculiar nature of Excel dates, but what IS the problem. I tried using DATEVALUE and DATE functions to get the serial number version of the date, but that didn't help. Yes, Dec 25th IS in the search list. :eek:
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

poorwallace

Active Member
Joined
Mar 8, 2005
Messages
365
I set up your situation on my end and things worked fine....so barring any additional info, I can't really say what might be happening. However, as far as the "FALSE" is concerned, from Excel help on Vlookup: "If FALSE, VLOOKUP will only find an exact match" ... it also mentions that your list won't need to be sorted (like you said), but given that the FALSE makes your vlookup a bit more rigourous, you might want to check to see if the values are EXACTLY the same...I suggest using the formula

=EXACT(B8,F9)

Where B8 is the cell location of Dec 25 in your B5:D100 array.

Ok, and given all that, posting your spreadsheet would still be pretty helpful.

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,014
Members
412,304
Latest member
citrus
Top