Formula Assistance required..
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
Best 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.
Answers
-
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.
-
Thanks Paul - really helpful. Can see how it works and it worked from the off!
Many thanks
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!