Explore. Ask. Answer.

or Explore Discussions

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

Gabby NepomucenoGabby Nepomuceno ✭
edited 07/08/21 in Formulas and Functions
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}))

Test sheet.jpg
test Assigned To.jpg


Tags:

Best Answer

  • Mark CronkMark Cronk ✭✭✭✭✭
    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 ✭✭✭✭✭
    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.

  • Gabby NepomucenoGabby Nepomuceno ✭

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