Forum Discussion

dapper49's avatar
dapper49
Copper Contributor
Nov 03, 2023

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.

 

  • 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.

    • dapper49's avatar
      dapper49
      Copper Contributor

      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.

  • KCP54's avatar
    KCP54
    Copper Contributor
    I'm assuming you have data for other months and they are working okay?
    • dapper49's avatar
      dapper49
      Copper Contributor
      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.
      • KCP54's avatar
        KCP54
        Copper Contributor
        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.
  • dapper49's avatar
    dapper49
    Copper Contributor
    The problem began with data I received midway through June, not June 1.

Resources