Index/Match Help

SherryFox
SherryFox ✭✭✭✭
edited 01/17/25 in Formulas and Functions

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:

Sherry Fox

Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies

EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

Core App Certified 🦊

NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

Connect with me on LinkedIn

Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.

Tags:

Best Answer

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    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
    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"

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

    Core App Certified 🦊

    NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

    Connect with me on LinkedIn

    Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion
    Answer ✓

    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
    SherryFox ✭✭✭✭

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

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

    Core App Certified 🦊

    NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

    Connect with me on LinkedIn

    Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!