Can an INDEX(MATCH) formula be built dynamically using @row data?

I am trying to build an INDEX(MATCH) formula in one column based on data in another column. I can work around this by using a huge IF statement but that exceeds the single cell capacity for a formula. Here is the basic formula:

INDEX({Cap - Week 1}, MATCH([CP-Key]@row, {Capacity - CP Key}, 0)

  • The "Cap - Week 1" is the dynamic part, specifically the number 1, possible values that may exist in the other column are 1 - 52 (the week # of the year).
  • How can I pull the @ROW value (1 - 52) and stick it into the INDEX formula so it looks like:
    • INDEX({Cap - Week 1}, MATCH . . .
    • INDEX({Cap - Week 2}, MATCH . . .
    • INDEX({Cap - Week 3}, MATCH . . .

Thanks,

Andrew B. Boston

Best Answer

  • Andrew B. Boston
    Answer ✓

    Hello again, just wanted to update you that your idea worked! Thanks very much. When reading the INDEX(MATCH()) description & samples, I wasn't picking up on how the optional [column_index] worked, I think more advanced examples would be greatly helpful.

    Thanks again,

    Andrew B. Boston

Answers

  • Paul Newcome
    Paul Newcome Community Champion

    You can't, but what you can do is reference all 52 week columns in a single reference then use some sort of cell reference to indicate which column number to pull from.

    =INDEX({Cap - All 52 Weeks}, MATCH([CP-Key]@row, {Capacity - CP Key}, 0), [Column Number]@row)

  • Hi Paul, thanks for the reply. It may work, but I need your help to better understand the solution. Are you suggesting that in "Manage References" I create a single relationship that includes the range of all 52 columns (all 52 weeks) and then tell the formula which column to pull back (like the VLOOKUP does)? Right now, I have 52 different relationships and refer to them separately. Please confirm.

    Thanks very much.

    Andrew B. Boston

  • Andrew B. Boston
    Answer ✓

    Hello again, just wanted to update you that your idea worked! Thanks very much. When reading the INDEX(MATCH()) description & samples, I wasn't picking up on how the optional [column_index] worked, I think more advanced examples would be greatly helpful.

    Thanks again,

    Andrew B. Boston

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!