Index Match Help
Good afternoon,
I'm trying to get my Index and Match formula to work, it was working correctly and has now broken and I'm not sure why.
I Have a "Weekly Metrics" Sheet I'm Trying to pull information over to the "Weekly Metrics 2" Sheet
I'm trying to get the data from Weekly Metrics to Weekly Metrics 2 by matching the dates
=INDEX({Weekly Metrics Range 1}, MATCH([Week 1]1, {Weekly Metrics Range 3}, 0))
Thank you for the help.
Answers
-
Hello @SAnkney
1Try changing MATCH([Week 1]1 to MATCH([Week 1]@row.
=INDEX({Weekly Metrics Range 1}, MATCH([Week 1]@row, {Weekly Metrics Range 3}, 0))
Michael
-
so you have several formulas for each column?
=INDEX({Weekly Metrics Range 1}, MATCH([Week 1]1, {Weekly Metrics Range 3}, 0))
=INDEX({Weekly Metrics Range 1}, MATCH([Week 2]1, {Weekly Metrics Range 3}, 0)) and so on?It looks like your formulas are working, because the average is showing. I think the issue is that your dates are being converted to text instead of date.
In your scenario, the easy implementation would be to create a new column in your source sheet called week helper. Make it a column formula with this:
=Week@row + ""
Now, in the metrics sheet, update the reference for {Weekly Metrics Range 3} to be the helper week column that is text.
Finally hide the helper column on the source sheet and continue using as normal.Hope this helps!
Sincerely,
Jacob Stey
-
Thank you for the help, the Average was being pulled differently.
I just ended up changing the The Week Column on the Weekly Metrics sheet to a text, and that seemed to fix it.
-
Awesome! happy to help.
I was going to consider that but sometimes people like the option to be able to click on the date from a calendar box haha
Sincerely,
Jacob Stey
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!