INDEX COLLECT function returning #INCORRECT COLUMN VALUE

I have a sheet that contains the info of all of my employees which includes a column for their initials. The training department of my company has a sheet that I have view access to but not editor access. They track the qualification due date of all of my employees and I want to collect the due date on my employee info sheet based on their initials. This is the formula I came up with:

=IFERROR(INDEX(COLLECT({Qualification Expiry Column in Training Sheet}, {Initials Column on Training Sheet}, Initials@row), 1), "No Match")

On any row that does not have matching initials I am getting "No Match"

On any rows with a match I am getting "#INVALID COLUMN VALUE"

And on one row I am getting a blank because the value being collected is blank.

Any Ideas as to why I'm getting an error?

Here's My Sheet:

Here's Trainings Sheet: (Highlighted the columns I'm using)

Find more posts tagged with

Formulas

collect

INDEX

errors

Accepted answers

Paul Newcome

Your formula is fine. Either the column you are putting the formula into is not a date type column or the column you are pulling from is not a date type column.

All comments

Darren Mullen

@aweber I don't have time right now to verify 100%, but I think what is happening is that by using COLLECT() it's transitioning the dates from your source sheet into text.

I'd say use Index( MATCH()) instead of Index(collect()) and that should fix your issue.

aweber

I am not super familiar with how match works. Here is the formula I came up with for the INDEX MATCH:

=INDEX({Expiry Column from Training Sheet}, MATCH(Initials@row, {Initials Column on Training Sheet}, 0), {Expiry Column From Training Sheet})

This is yielding a #INVALID DATA TYPE error instead of the error I was having previously.

I just noted that the expiry date that I am trying to index is a calculated value, would that effect if it can be indexed?

Paul Newcome

Your formula is fine. Either the column you are putting the formula into is not a date type column or the column you are pulling from is not a date type column.

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.