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.

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.