Formula Assistance required..

Ryan Rooke

Hi

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

Thanks

Paul Newcome

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.

Paul Newcome

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.

Ryan Rooke

Thanks Paul - really helpful. Can see how it works and it worked from the off!

Many thanks

Paul Newcome

Happy to help. 👍️