Incorrect Index Match Data being returned
Me again!
I have youtubed hours of videos and cannot figure out what I am dong wrong. I have a unit name that I would to automatically return a unit number from our apparatus inventory. The formula seems to function but the fleet number does not match the apparatus name from the reference table. For Squad 2 should be unit U72001 not U72001 and Engine 8 should be U72060 not U72122. Thanks in advance to the great members of this form.
Answers
-
I have to admit I'm not quite sure why the MATCH is finding the wrong value - it may have to do with how the numbers are similar and it's finding an approximate match instead of exact.
I would suggest trying an INDEX(COLLECT formula instead, as you can specify bringing back the first row where there's an exact match... try this:
=INDEX(COLLECT({CCFD Apparatus Master List Range 3}, {CCFD Apparatus Master List Range 2}, [Apparatus or Unit]@row), 1)
The 1 at the end identifies the first row that has [Apparatus or Unit]@row
If this is still bringing back incorrect results, I'd be interested to know if it's finding more than one row with your criteria on it. Can you try this to test, it should just return 1 for each row:
=COUNTIF({CCFD Apparatus Master List Range 2}, [Apparatus or Unit]@row)
I would also suggest re-creating the formula from scratch and manually identifying each of your ranges/columns again in case the first selection missed a few rows from that column somehow.
Let me know if any of this has helped!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 287 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!