Solved MS Excel 2010 Vlookup Date Problem

March 16, 2017 at 20:13:38
Specs: Windows 7
I have a date tab with dates (Column A "dd/mm/yyyy", Column B "MMM-yy" and Column C "yyyy-yy" [financial year]).

I work in a hospital. I have a list of surgeries with the date ("dd/mm/yyyy") they were performed.

I have a Vlookup to obtain the "MMM-yy" data (for my monthly pivot tables) as follows:

=IF(ISNA(VLOOKUP($B39,Dates,2,0)),"",VLOOKUP($B39,Dates,2,0))

I came across an error in my source tab - all dates in March 2017 had "MAR-16" in Column B. I changed these, manually, to "'MAR-17".

The Vlookup continues to work every single month but for MAR-17.

When formatted as General (as all the others are), it returns "42795". When I change it to Custom format "mmm-yy" (which none of the others, which work fine, are), it returns "Mar-17" but in the pivot table comes up as "1-Mar-2017" whereas all the others are "FEB-17" etc).

Any help would be great.

Thanks in advance!


See More: MS Excel 2010 Vlookup Date Problem

Reply ↓  Report •

#1
March 17, 2017 at 05:02:35
I'm not near Excel right now to try anything, but my guess is that all the working "dates" are not actual dates as far as Excel is concerned. My guess is that they are actually text, which may explain why they don't show up in your pivot table with the "day" portion or display the serial number like the Mar-17 cells do.

Excel can be weird when it comes to dates/text, especially if the "dates" were imported from another source. They often come over as text and is can be troublesome to convert them to dates that Excel will recognize.

In your case, you may have the opposite problem. I think you have to change your Mar-17 to Text in order for your VLOOKUP to work like the others. That can also be troublesome because Excel will often be reluctant to give up a Date format once it is set. You could try adding a single quote before a few of your Mar-17 entries to force them to Text and see how Excel handles them.

Like I said...just a guess.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
March 17, 2017 at 06:46:01
✔ Best Answer
BTW...this is not directly related to your question...it's just a suggestion about your VLOOKUP formula.

The IFERROR function was introduced in Excel 2007. This makes writing VLOOKUP formulas a little easier:

=IFERROR(VLOOKUP($B39,Dates,2,0),"")

This formula will return the empty string if the lookup_value isn't found in the lookup_table.

Now for the downside of using IFERROR:

The standard method, using = IF(ISNA...) directly checks for the ISNA error returned by VLOOKUP. The IFERROR function looks for any error related to the VLOOKUP.

The implication of this in your case is as follows:

=IF(ISNA(VLOOKUP($B39,Dates,2,0)),"",VLOOKUP($B39,Dates,2,0))

There are 2 possible errors that that formula can produce: #N/A and #NAME. Your formula will handle the #N/A error and return the empty string, but if the Named Range "Dates" doesn't exist, it will return the #NAME error. That's a good thing since you'll know that you are referencing an invalid range.

The IFERROR function will return the empty string for both of those errors but you will not know why. Let's take the situation where the user thinks he created the named range but forgot. When the IFERROR returns the empty string, he might think it's because the lookup_value doesn't exist. That's not a good thing.

So, considered yourself warned ;-). IFERROR can save some typing, but it does have some inherent dangers.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •
Related Solutions


Ask Question