VlookUp not working if column a date format

Lyn Pringle

Hi

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?

Thanks

Lyn

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, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

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!

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