maderitetech
asked on
Excel VLookup Not Finding Values that Look Like Dates
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
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)
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.
=VLOOKUP(A2,Sheet2!$A$2:$C
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)
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.
andrew_man
Would you mind to post your worksheet here?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this,
=VLOOKUP(IFERROR(IF(DATEVA
But, you should well prepare of your data, if needs.
=VLOOKUP(IFERROR(IF(DATEVA
But, you should well prepare of your data, if needs.
ASKER
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.
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.