Index Match Error

Are you able to help me with a formula problem?

I am using a combination of MID and Right functions to pull a number from a text string....

Manager Name = Karri (800014723)

Manager ID =MID(RIGHT([Manager Name]@row, 11), 2, 9)

Manager ID = 800014723


Now I am trying to use the Manager ID cell where 800014723 was returned in an index/ match formula. I keep getting the match not found error. But if I put 800014723 in as simple text in a cell, it works and returns the correct value.

=IFERROR(INDEX({Staff - Email}, MATCH([Manager ID]@row, {Staff - ID}, 0)), "Employee Email Not Found")

What am I missing? Thoughts?


Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Katherine Britt

    Your Match function is looking up the text 800014723 and trying to find a match, but in your source data it is stored as a number.

    Add the VALUE function to your Manager ID column, and that will convert it to a number.

    =VALUE(MID(RIGHT([Manager Name]@row, 11), 2, 9))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!