Index Match Help

SAnkney
SAnkney ✭✭✭
edited 04/26/24 in Formulas and Functions

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.

Tags:

Answers

  • MWilkesen
    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
    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!

    Sincerely,

    Jacob Stey

  • SAnkney
    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
    SteyJ ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!