Solved

Excel VLookup Not Finding Values that Look Like Dates

Posted on 2013-12-03
6
297 Views
Last Modified: 2013-12-03
I am having trouble with vendor item numbers that look like dates, for example 10-31. When I try to do a VLookup on the row containing that value, I always get #N/A as a result, even though the value exists on the sheet. My original formula was as follows.

 =VLOOKUP(A2,Sheet2!$A$2:$C$500,3,FALSE)

That worked fine for vendor item numbers that were alpha-numeric, but not for numeric values. I change it to the following which works fine for everything but one item number that looks like a date (10-31).

 =VLOOKUP(IFERROR(VALUE(A2),A2),Sheet2!$A$2:$C$500,3,FALSE)

I have to stop the macro, manually fix the error by typing in the value instead of doing a VLookup, and then finish the macro.

I have tried formatting both columns as text before running the macro, but that makes no difference.

I'm at a loss to know how to fix this, and could really use some help.
0
Comment
Question by:maderitetech
6 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39692572
Would you mind to post your worksheet here?
0
 
LVL 14

Assisted Solution

by:Faustulus
Faustulus earned 250 total points
ID: 39692719
Your original formula works fine for me where both columns are formatted as Text.
Could it be that you are shutting the stable doors after the horses have escaped?
Once Excel decides that "10-31" is a date the entry is converted to "Oct 31" with a cell value of 41578, the year 2013 being presumed. Once that change has been made, whether for the lookup value or in the lookup range formatting either to Text won't undo the damage. The cell formatting must be applied before the data is entered.

Is the formula applied by your macro? If so, is there perhaps a chance that the data conversion takes place in VB rather than on the worksheet?
0
 

Author Comment

by:maderitetech
ID: 39692854
I have attached a sample workbook that shows the problem on Sheet3. Both Sheet1 and Sheet2 were imported by macros (macros not included). From this point on both sheets stay in the workbook. They can be edited, but not deleted.
I did not know that cell formatting must be applied before the data is entered. The macros do not format the cells before importing.
The VLookup formula is applied by the macro.
EE-Sample.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 total points
ID: 39692907
Try

=IFERROR(VLOOKUP(C2,Sheet2!$A$2:$C$17,3,FALSE),VLOOKUP(VALUE(C2),Sheet2!$A$2:$C$17,3,FALSE))
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39692991
Try this,

=VLOOKUP(IFERROR(IF(DATEVALUE(C2),C2),IFERROR(C2+0,C2)),Sheet2!$A$2:$C$17,3,FALSE)

But, you should well prepare of your data, if needs.
0
 

Author Closing Comment

by:maderitetech
ID: 39693108
The formula given by ssaqibh worked perfectly without having to check to see if the value was a date. That was impressive!
I was also impressed with the answer from Faustulus which explained what was causing the problem. (The cell formatting must be applied before the data is entered.)
Thanks to all for you help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now