# Formula Help

Options

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))

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

• Options

Thank you and ill try that!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

@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")

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!