Forum Discussion
VLOOKUP
I receive date each month on funds, with their unit value. Beginning in June, the data I received came from a slightly different method, and now my worksheet cannot pick up values using VLOOKUP. It looks like the DATE format has been changed, but I cannot figure out how to work around it.
Thank you for any help you may offer.
A sample of my worksheet is attached.
12 Replies
The values in A12 and down are text values - they are left-aligned if you look carefully.
The same goes for the values in B12 and down.
Select A12:A21.
On the Data tab of the ribbon, click Text to Columns.
Select Delimited, then click 'Next > ' twice.
Select MDY from the date drop-down, then click Finish.
The values will be converted to 'real' dates.
Next, select B12:B21.
On the Data tab of the ribbon, click Text to Columns.
Select Delimited, then click Finish.
The VLOOKUP formula should now work as intended.
Thank you for the help. I think I have col B fixed correctly. However, when I followed your instructions for col A, I have it split into 3 columns as shown. I must have done something wrong on the "text to columns" procedure. I am certain you can steer me in the right direction.
Thank you.
When you convert the dates, make sure that all the check boxes for delimiters in step 2 are cleared.
Step 3:
Result attached.
- I'm assuming you have data for other months and they are working okay?
- Yes, I think it works okay for dates prior to June 16, when the data was coming from a different source. At least, it was produced by a different procedure which has worked fine for the last several years.
- I hope you see my second reply. I replied to myself. LOL. Here it is again...
What I just explained is one way to do it. NOT the easiest. I didn't quite get what Hans was doing, but now that I do, that is the EASIEST way to do it. Just remember on the very first screen, choose "Fixed" width. This will stop Excel from automatically using the "/" as a delimiter giving the result you got.
- The problem began with data I received midway through June, not June 1.
