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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!