Match

Options

=INDEX({Operations Efficiency KPI's Range 9}, MATCH(IF([Count 7]2, {Operations Efficiency KPI's Range 3}, [Count 6]2, {Operations Efficiency KPI's Range 2})))


What am I doing wrong?

I want the cell to match if the cells match

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Samuel Dowdy Jr.

    The syntax above is incorrect for an index/match. However, regardless of syntax, an index/match is typically for only one criteria. You have two. In this instance the Index/Collect is what you need. It’s syntax is =INDEX(COLLECT({range you’re wanting to collect}, {range1}, criteria 1, {range2}, criteria 2, etc), row index)

    =INDEX(COLLECT({Operations Efficiency KPI's Range 9}, {Operations Efficiency KPI's Range 3}, [Count 7]@row, {Operations Efficiency KPI's Range 2}, [Count 6]@row),1)

    I changed your Row number from a specific designation to @row to make your formula more robust.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Samuel Dowdy Jr.

    The syntax above is incorrect for an index/match. However, regardless of syntax, an index/match is typically for only one criteria. You have two. In this instance the Index/Collect is what you need. It’s syntax is =INDEX(COLLECT({range you’re wanting to collect}, {range1}, criteria 1, {range2}, criteria 2, etc), row index)

    =INDEX(COLLECT({Operations Efficiency KPI's Range 9}, {Operations Efficiency KPI's Range 3}, [Count 7]@row, {Operations Efficiency KPI's Range 2}, [Count 6]@row),1)

    I changed your Row number from a specific designation to @row to make your formula more robust.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!