Index and Match returns one incorrect response...all others correct

07/08/21 Edited 07/08/21
Accepted

I have a service request sheet with an index/match formula to assign a person based on request type. My formula works for all request types except "External Communications" - it assigns the wrong person. I tried creating a new "Assigned To Lookup" reference sheet, manually entering all fields, and yet the error persists.

=INDEX({Assigned To Lookup Range 1}, MATCH([Request Type]@row, {Assigned To Lookup Range 2}))


Tags:

Best Answer

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    Try:

    =INDEX({Assigned To Lookup Range 1}, MATCH([Request Type]@row, {Assigned To Lookup Range 2}, 0))

    The 0 says your list isn't sorted so it needs to find an exact match.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    Try:

    =INDEX({Assigned To Lookup Range 1}, MATCH([Request Type]@row, {Assigned To Lookup Range 2}, 0))

    The 0 says your list isn't sorted so it needs to find an exact match.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • @Mark Cronk Yes! Thank you! It was driving me nuts trying to figure out why it wasn't working. Our comms team thanks you! -Gabby

  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi Gabby, Glad you found a solution. Thank you for contributing to the community and for the vote up.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Sign In or Register to comment.