Index/Match with 2 sheets

Hi all!

First, thanks for taking the time to help me, it is greatly appreciated. I am trying to do an INDEX/MATCH since I can't use XLOOKUP (and this is to the left). First, I created a Unique ID with the JOIN feature to help me accomplish this. That formulas is:

=IF([Release Name]@row = "", "", JOIN([Release Name]@row:Release@row, " | "))

And it is on BOTH my sheets I am working on.

New: Release Update Sheet - source data

Date Metrics - calculations

Okay I am in the Date Metrics sheet, and my formula is as follows:

=INDEX({Target Duration}, MATCH([Release Name & Number (Helper)]@row), {Unique ID - Release}, 0))

My formula results in an "Unparsable" error, and I am not sure why, or how to fix it. I know how to do Index/Match in Excel, but something appears to be different.

Target Duration is the range name I gave for that one column on the New: Release Update Sheet. Which is the data I want to display on the Date Metrics sheet when the match is made.

Release Name & Number (Helper)]@row) is from the Date Metrics sheet where I am making the calculation

Unique ID - Release is the same formula that generates the Unique ID on the other sheet

Sherry Fox

Project Analyst | Core Quality Services (QMS Transformation)

Medtronic

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Tags:

Answers

  • Anson Cheung
    Anson Cheung ✭✭✭✭

    Hi Sherry,

    For #UNPARASABLE error, it comes to a formula with a misspelling column name or incomplete operator, which prevents Smartsheet from parsing. I notice that you have a redundant closing bracket in your formula. Would you please try out the below formula to see if there is any further error?

    =INDEX({Target Duration}, MATCH([Release Name & Number (Helper)]@row, {Unique ID - Release}, 0))

    --Anson

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @Anson Cheung ,

    If I am correct, when I did the formula in error I accidentally added a closing parenthesis after "[Release Name & Number (Helper)]@row", is that correct? Your formula worked of course. I just want to know what I did wrong to prevent it in the future. Thank you so much for your help!!!!!!

    =INDEX({Target Duration}, MATCH([Release Name & Number (Helper)]@row), {Unique ID - Release}, 0))

    Sherry Fox

    Project Analyst | Core Quality Services (QMS Transformation)

    Medtronic

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!