Index Match Help

SAnkney

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.

MWilkesen

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

SteyJ

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!

SAnkney

@SteyJ

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.

SteyJ

https://community.smartsheet.com/discussion/comment/433309#Comment_433309

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