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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul McGuinness
    Paul McGuinness Overachievers

    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!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    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! 🙂

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!