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

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!

Find more posts tagged with

Formulas

columns

INDEX

MATCH

Accepted answers

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?

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.