Formula Help

ethan.lewis

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

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)

Paul McGuinness

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

ethan.lewis

Thank you and ill try that!

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

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