VlookUp not working if column a date format


We are trying to execute a VLOOKUP based on a date column.

When we update the column to text, the vlookup works, however when the column is formatted as DATE, we get "INVALID COLUMN VALUE" errors.

Anyone know how to resolve this?




  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Lyn Pringle

    I hope you're well and safe!

    Can you share the formula here?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Be safe and have a fantastic weekend!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Lyn Pringle
    Lyn Pringle ✭✭✭

    @Andrée Starå 

    Thanks for your reply.

    Users capture details via a form and indicate their last visit date when submitting a new request for a visit approval.

    We run an approval workflow on this sheet. When an entry is approved, it is moved to another sheet leaving only new submissions.

    The sheet the data is moved to looks exactly the same as the original input sheet, but contains only approved records.

    When rows are moved to this approved sheet and a VLOOKUP is done on the VISIT DATE field, the forumla returns an error. If we change the date field to text, the VLOOKUP works, however the date format is then altered

    Original Submission Sheet

    You can see in the 2nd screenshot that Visit Date has changed to 07/04/21 where in the first screenshot it is 04/07/21

    Looking forward to hearing back from you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!