Formula Help
I am using an index & match formula to cross reference another sheet for data plugins. My problem is some of the data that I need is coming back as a #NO MATCH which is understandable because some of the information is not available. There are other sheets that do have the same criteria available that I can match too but I'm unsure how to set the formula to find it. if it doesn't find it on the first sheet then it will need to look on the second and third sheet. My original formula is this.
=INDEX({Raw Material name}, MATCH([RAW MATERIAL ID]@row, {Raw Material Inventory Range 1}, 0))
Answers
-
You would create multiple INDEX/MATCH formulas (one for each of the different reference sheets) and then string them together with IFERROR statements.
=IFERROR(IFERROR(IFERROR(INDEX/MATCH sheet 1, INDEX/MATCH sheet 2), INDEX/MATCH sheet 3), INDEX/MATCH sheet 4)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi @ethan.lewis
You could use a nested IFERROR -
Something like =IFERROR(INDEX({Raw Material name}, MATCH([RAW MATERIAL ID]@row, {Raw Material Inventory Range 1}, 0)),IFERROR(Second formula here,IFERROR(Third formula here etc.
Hope that helps
Thanks
Paul
-
Thank you and ill try that!
-
Hi @ethan.lewis,
Use a nested IFERROR where the value if error is the second/third sheet. Something along the lines of this for 2 sheets:
=IFERROR(IFERROR(INDEX({Raw Material name}, MATCH([RAW MATERIAL ID]@row, {Raw Material Inventory Range 1}, 0),(INDEX({Raw Material name}, MATCH([RAW MATERIAL ID]@row, {Raw Material Inventory Range 2nd sheet}, 0),"Not found").
If you have a third sheet, then you would add yet another IFERROR and shuffle the "Not Found" to the end there (you can obviously replace this as desired).
Hope this helps, if you've any problems/questions then just post! 🙂
-
@Nick Korna Looks like your parenthesis are off a bit.
=IFERROR(IFERROR(INDEX({Raw Material name}, MATCH([RAW MATERIAL ID]@row, {Raw Material Inventory Range 1}, 0)),(INDEX({Raw Material name}, MATCH([RAW MATERIAL ID]@row, {Raw Material Inventory Range 2nd sheet}, 0))),"Not found")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K 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
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!