Fix #INVALID COLUMN VALUE error when using Index Match in a different column type than source sheet

xtm.ayala

Hello! I am curious if there is a solve to the #INVALID COLUMN VALUE error I get when trying to pull a value from another sheet using Index Match, and both the source sheet and current sheet have different column types.

I am currently using the following formula:
=IFERROR(INDEX({OChM - Intake - Intake Meeting Date}, MATCH([Task Description]4, {OChM - Intake - Intake #}, 0)), "n/a")

Source Sheet is named: OChM Intake
Current Sheet is named: Project

In the Source Sheet, OChM Intake, I am looking to match and return a value for the Intake Meeting Date. The column type is Date/Time.

In the Current Sheet, Project, the above formula is in a single cell of the column (named Task Description) where the column type is Text/Number.

From what I understand, it is NOT POSSIBLE to change the type for a single cell in the column.

Given this information, is there a way the value can be manipulated within the formula to instead show as text? I've done something similar in excel; curious if this can be done similarly in SmartSheet.

Confirming I am also the Owner of the workspace and sheet.

Current Sheet: Project

Source Sheet: OChM Intake

Thanks in advance for any guidance!

Gillian C

Hi @xtm.ayala

So short answer no you can't change the column type for a single cell, however you can change the format of your answer.

You could do

=IFERROR((INDEX({OChM - Intake - Intake Meeting Date}, MATCH([Task Description]4, {OChM - Intake - Intake #}, 0)) + "", "n/a")

And this would convert your date value to text.

If you need to then use the date as text format as a date referenced somewhere else you should be able to do =DATE(cell value) to convert it back to a date format.

Hope that helps?

Gillian C

Hi @xtm.ayala

So short answer no you can't change the column type for a single cell, however you can change the format of your answer.

You could do

=IFERROR((INDEX({OChM - Intake - Intake Meeting Date}, MATCH([Task Description]4, {OChM - Intake - Intake #}, 0)) + "", "n/a")

And this would convert your date value to text.

If you need to then use the date as text format as a date referenced somewhere else you should be able to do =DATE(cell value) to convert it back to a date format.

Hope that helps?

xtm.ayala

Hi Gillian! Thank you for the reply. This worked perfectly. Appreciate the help!