Solved: What Does VLOOKUP Compare to??

[Cyberdude]

I was trying to do a date search with VLOOKUP and I never could get it to work. But it raised a question in my mind about what VLOOKUP compares its search argument to. It appears that it looks at the displayed values in the search list as opposed to the values stored in memory. Normally it wouldn't matter, but when your search list contains dates it just might.

If I have a list of dates that are monotonically increasing in value, i.e., every date is greater than or equal to the one that precedes it, then the stored date serial numbers WILL be sorted in ascending sequence, because of the way Excel stores dates. However, the dates that are displayed more often than not are NOT in ascending sequence ("Feb" is less than "Jan", etc.). Hence my question: which values are examined by VLOOKUP??

[Paleo]

As far as I know it looks at the cells value, so doesnt matter its formating.

[Bob Phillips]

I agree with Paleo, the format is irrelevant, the values count.

What leads you to the opposite conclusion?

[Ken Puls]

And how is "Feb" less than "Jan"? If you're referring to the alpha order, then you're actually not looking at dates, but rather text strings. If you have a date format (mmm) on the cell, (which means they're numbers,) then the only way Feb can be less than Jan is if it is from a prior year...

[Cyberdude]

Ken, I was saying that a list of dates that are formatted to be like:
Jan 1, 2005
Feb 1, 2005
are not in ascending alphabetic order because the letter "J" comes AFTER the letter "F". So IF VLOOKUP were searching the displayed list (not the stored list), then it wouldn't work, at least with the TRUE option. Since I haven't been successful using VLOOKUP in a date search, I'm just thinking about all possibilities for why I'm having trouble.

Date lists often give me fits. I've found that sometimes I have to manually reformat then rewrite a whole list one date at a time in order for the dates to become dates. I have no idea why that happens sometimes.

[Ken Puls]

Ah, okay.

Bob and Carlos are correct, to my knowledge. The vlookup works on the underlying value, not the format.

Now, out of curiosity, do you add the ,False to the end of the vlookup?

[Cyberdude]

Well, I just had a successful VLOOKUP with dates. I have no idea why I was having problems, but let's drop it now.
Thanks for the comments, guys.

[Cyberdude]

Ken, yes I do use FALSE when appropriate. Actually, I now believe (but can't prove) that I must have had my range improperly specified. :omg2: