Index/Match Help

Since Excel created XLOOKUP, I have lost my ability to properly do index/Match, I am getting an #INVALID COLUMN VALUE error. First here is my formula:

=INDEX({Deliverable Tracker Approved Archive_Delivery Liaison download complete}, MATCH(Help@row, {Deliverable Tracker Approved Archive_Help2}, 0))

This screenshot is from my file named Deliverables tracker. It is also where the Index/Match formula is.

These are just temporary columns while I capture the data. My other sheet where I am getting my data from is called Deliverable Tracker Approved Archive. On that sheet, this is the unique key and then the data I am trying to capture:

Find more posts tagged with

Formulas

Accepted answers

jmyzk_cloudsmart_jp

Is the image below what you want?

In that case, use a Text/Number column, [Upload Status] for example, and modify the formula like this;

[Upload Status]=IF(INDEX({Deliverable Tracker Approved Archive_Liaison downl}, MATCH(Help@row, {Deliverable Tracker Approved Archive_Help2}, 0)), "Upload Complete", "")

https://app.smartsheet.com/b/publish?EQBCT=9ae6b3f52b0a46f79680641c73b974ad

All comments

jmyzk_cloudsmart_jp

Hi @SherryFox

Perferpse your Help4 column type is wrong; it is not a checkbox, but a Text/Number of something.

In your formula below, the INDEX function references a Checkbox column, which returns a True or False value.

=INDEX({Deliverable Tracker Approved Archive_Liaison downl}, MATCH(Help@row, {Deliverable Tracker Approved Archive_Help2}, 0))

However, if your Help4 column's type is text/number, you assign the column the wrong type values, true/false. Thus, you get the "#INVALID COLUMN VALUE" error.

For example, in the [Help 4 - Text/Number + ""] column, I added '''' to your formula.

=INDEX({Deliverable Tracker Approved Archive_Liaison downl}, MATCH(Help@row, {Deliverable Tracker Approved Archive_Help2}, 0)) + ""

As the image below shows, it returns true/false values.

Please check if the column type matches the type of column you are referencing with the INDEX formula.

https://app.smartsheet.com/b/publish?EQBCT=9ae6b3f52b0a46f79680641c73b974ad

Sample data sheet

https://app.smartsheet.com/b/publish?EQBCT=cd872e7a293e46499488d85ac5a75c83 (This published sheet is editable, so you can check how the formula works by checking / un-checking)

SherryFox

No that is not working right. I need to pull the values from the checkbox column (Liaison download complete) from the Deliverable Tracker Approved Archive into the result for the Deliverable Tracker. There have been some changes on the second sheet, so I would love a TRUE (box is checked) to equal "Upload Complete"

jmyzk_cloudsmart_jp

Is the image below what you want?

In that case, use a Text/Number column, [Upload Status] for example, and modify the formula like this;

[Upload Status]=IF(INDEX({Deliverable Tracker Approved Archive_Liaison downl}, MATCH(Help@row, {Deliverable Tracker Approved Archive_Help2}, 0)), "Upload Complete", "")

https://app.smartsheet.com/b/publish?EQBCT=9ae6b3f52b0a46f79680641c73b974ad

SherryFox

That is not working; apparently my initial helper columns are not unique, so no matches are being created

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.