using VLOOKUP with Dates and Text

Umesh Shah

I have two sheets, one which keeps track of dates and reminder dates which is maintained by team and looks like this:

and another sheet which drives a form where people pick dates (a copy of the dates in Long Date above).

The "target Appointment Date" is a drop-down value field i.e. TEXT.

In the field "reminder 1" i would like to do a lookup to the dates field above. I would like to lookup the drop-down value in "Targeted Appointment Date" and bring back the "Reminder 1" date (which is a date field on both sheets).

I know the problem is that the field i am trying to match is a text field, while the table has all date fields. How can i fix this without having the change the table to text.

Paul Newcome

@L_123 I don't even remember what I worked on yesterday let alone a year ago. Hahaha. I'll take a shot though.

We have a date type column (stored on back end as mm/dd/yyyy) but displayed as the long date, and we need to match this to a text value that only looks like the long date. So what we need to do is figure out a way to convert one of those entries into the other data type.

I am thinking it will be easier to convert the date into a text value. We can do that either with a couple of nested IFs (ugly) or with a reference table (preferred).

I would have the reference table set up like this:

Then we can string together some INDEX functions like so:

=INDEX({Day}, WEEKDAY([Long Date]@row)) + ", " + INDEX({Month}, MONTH([Long Date]@row)) + " " + DAY([Long Date]@row) + ", " + YEAR([Long Date]@row)

This should give you a text string that mimics the Long Date, and since it is a text value, it should be able to match when referenced by the dropdown column value.