IFERROR and MATCH Formula not pulling in formation - Prodactive S-Curve

jtahan

hello,

i came across a Video by Prodactive about S-Curves in smartsheet and so far everything has been working exactly as expected with one major exception; the "Actual" baseline. From the video and the downloaded examples, i used the exact same formula and referenced my sheet name versus what he had in his example. But for some reason i cannot get the data i need to pull. the way its intended to work is My S-Curve Archive sheet is updated weekly through an automation on another sheet with the current date and completion percentage (S-Curve Archive Range 1). Now when that archive sheet is updated, my Metrics sheet should see a matching Yr-Wk value and pull in the related percentage. but when i do it now, i just get blanks. I even tried changing the Yr-wk cell to A on the Metrics and the Archive sheet to A and it still wont pull. i tried changing the type of field from formula to static but still nothing. The formula in this cell is currently:

=IFERROR(INDEX({S-Curve Archive Range 1}, MATCH(Date@row, {Date}, 0)), "") on my Metrics sheet

The Yr-Wk formula on that same metrics sheet is:

And the Yr-wk formula on the referenced Archive sheet that should be pulling in the % complete column is:

Any Idea what i could be doing wrong or what im missing?

Paul Newcome

I would start with removing the IFERROR piece from the IDNEX/MATCH so you can see if it is finding a "match" and pulling in a blank or if there is some issue with the INDEX/MATCH and the IFERROR is masking it with a blank.

jtahan

im getting an invalid reference when i just have the below formula

also i didnt mention it before but when i reference the other Archive sheet range im only selecting the percentage column i care about, is that the correct way to use the index command or should i be selecting the entire range like a vlookup would work?

Paul Newcome

You should only be referencing the single column, but that particular error means that a {Cross Sheet Reference} hasn't been set up properly. Double check your other cross sheet reference as well.

jtahan

thank you! that helped me realize i was never referencing the other Archive sheets Yr-wk column (bolded below)… it was just typed out in in the match formula with no reference. It is working now and its working with the iferror prefix. thank you so much.

my new formula now:

=IFERROR(INDEX({S-Curve Archive Range 1}, MATCH([Yr-wk]@row, {S-Curve Archive Range 2}, 0)), "")

Paul Newcome

Happy to help. 👍️