VLOOKUP

[dapper49]

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.

[#2]

The problem began with data I received midway through June, not June 1.

[#3]

I'm assuming you have data for other months and they are working okay?

[#4]

@dapper49 

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.

[#5]

@HansVogelaar 

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.

[#6]

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.

[#7]

@dapper49 

When you convert the dates, make sure that all the check boxes for delimiters in step 2 are cleared.

Step 3:

Result attached.

[#8]

I agree with Hans Vogelaar that the data from 3/16/2023 and down, both columns, are text values not Date and Number values. I was able to recreate your issue. Depending on if your data is imported directly to your table or not, will depend on how many steps you will need to take. One step you will have to take is converting to Dates and Numbers, You do this with two different formulas, =DATEVALUE and =NUMBERVALUE. They are both fairly straight forward to use and the "Help Using This Function" on the lower left corner of the Dialog Box will walk you through it. When you use the =DATEVALUE function, you will get a number as the result. Just use Format Cells to format as a Date. Reach out if you need more help on this Good Luck.

[#9]

@HansVogelaar 

Thank you, Hans, that works perfectly! Thank you so much for your expertise and help.

Dapper49

[#10]

Thank you for your help and suggestions. I will experiment to become more familiar with the procedure to make this adjustment each month.

[#11]

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.

[#12]

Great solution! I've used this many times but not how you used it. I didn't get it at first, but when I tried it, pretty darn slick. I learned something new from this. Every once in a while my data comes in as text. I'll use this method to convert it. THANK YOU!

[#13]

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.