Index to equal exact matches

I created a formula that should look for a specific cell within a range from another sheet, and report back another column. However, it does not search an exact match.

Formula: =INDEX({Res Sales Metrics Month}, MATCH([Date2]@row, {Res Sales Metrics Year-Month}), 0)

I want my month name column to report back the actual month name.

The sheet it is referencing is:

What is happening, is that if any "Date2" is matched with just '2018 / 1*", it pulls the first option, which is January.


Any ideas how i can make my index be an exact match before it pulls back an answer?

Best Answer

  • Mtmoroni
    Mtmoroni ✭✭✭✭✭
    Answer ✓

    Wow..... Finally figured it out....

    Here was my formula i was having countless hours of problems on:

    =INDEX({Res Sales Metrics Month}, MATCH([Date2]@row, {Res Sales Metrics Year-Month}), 0)

    Here's the formula working great!:

    =INDEX({Res Sales Metrics Quota}, MATCH([Date2]@row, {Res Sales Metrics Year-Month}, 0))


    Yes - it was the placement of a ")"..... Hours gone.....

Answers

  • Mtmoroni
    Mtmoroni ✭✭✭✭✭
    Answer ✓

    Wow..... Finally figured it out....

    Here was my formula i was having countless hours of problems on:

    =INDEX({Res Sales Metrics Month}, MATCH([Date2]@row, {Res Sales Metrics Year-Month}), 0)

    Here's the formula working great!:

    =INDEX({Res Sales Metrics Quota}, MATCH([Date2]@row, {Res Sales Metrics Year-Month}, 0))


    Yes - it was the placement of a ")"..... Hours gone.....

  • Glad you sorted it out!

    Placement of parentheses can definitely be tricky, and easy to miss. The default for MATCH without an indicator (like the 0 in the right place) is 1 (see here for more information).

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • shio
    shio ✭✭

    Thank you for sharing🤸‍♀️! I was having headaches for two hours straight!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!