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
-
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))
Answers
-
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))
-
Thank you thank you thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!