Index to equal exact matches

Options

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 ✓
    Options

    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 ✓
    Options

    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.....

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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).

  • shio
    shio ✭✭
    Options

    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!