VLOOKUP Problem Searching a List of Dates

[SidBord]

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.

 

[joelnichols]

Can you post your sheet?

 

[poorwallace]

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

 

[Pekkavee]

http://www.mrexcel.com/board2/viewtopic.php?t=186165&sid=fc1463009f3188be0c4987bf17c0e405