Formula Assistance required..



Has anyone found a way to look up the 'latest' value in a table similar to the above? I would like to pull the current month figure into [current month 5] where [current month 3] matches in the range of [Month 1 to 12] row 3. And then copy this for rows 6 to 9. This was the trend is being tracked and i can show current month separately on its own


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something like this...

    =INDEX([Month 1]@row:[Month 12]@row, 1, MATCH([Current Month]$3, [Month 1]$3:[Month 12]$3, 0))

    We use the INDEX function to pull from a range. In this case it is the current row across Months 1 - 12 columns. Since we are only indexing from a single row, the second portion of the INDEX function (row to pull from) is set as 1. Then to automate which column to pull from (3rd portion of INDEX function), we use the MATCH function. MATCH will return a number based on where within a range a specific value is found. When looking across a single row, the MATCH function will return the column number.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!