Date Formatted Column and Index Match to an Exact Date

MMiles

I have a column that others fill in with a date, date column type is the easiest to prevent having to do a lot of clean up as rows are edited over time and dates may push to another month. I can also restrict the column to a date.

If I am trying to use the index match formula below on a date column type it returns "blank" however if I change the Estimated Close Month column type to text it will work. My next thought was to have 2 columns for Estimated Close Month, one the team would edit which would be a date column type and the second a formula to equal the team edited column as a text column type. The results ended up with an error if both columns were not the same column type.

Anyone have any suggestions on a workaround for this?

Formula used- =IFERROR(INDEX([Monthly Recurring Revenue]@row, MATCH([Estimated Close Month_Use Last Day of Month]@row, "11/30/22", 0)), "")

• Estimated Close Month as date column type (formula does not return any results)

• Estimated Close Month as text column type (desired results but not desired column type for the Estimated Close Month)

• Error if using two different column types.

• If formatted as the same column type it works

Thanks 😀!!!

Paul Newcome

Try replacing the date with a cross sheet reference pointing to the date column in your source sheet.