edited 12/09/19 in Formulas and Functions

When we first started using Smartsheet, the rep who handled our "Launch Package" created the following formula.

=IFERROR(IFERROR(INDEX({SupplierItemNumber-AL}, MATCH($[Our Part Number]@row, {OurPN-AL}, 0)), INDEX({SupplierItemNumber-MZ}, MATCH($[Our Part Number]@row, {OurPN-MZ}, 0))), "ERROR - CHECK PART#")

It searches two sheets for "OurPN" and when it finds a match it returns the Supplier PN.  The problem is there may be multiple suppliers that have their own "supplier PN" for "OurPN".  So, it returns the first match it finds. 

I want to be able to search multiple sheets and match "OurPN" and a specific supplier then return the supplier PN. 

Not sure if INDEX(MATCH) or JOIN(COLLECT) would work better.  Any suggestions? 


  • I talked to Technical Support and they came up with a formula that would work for my situation.

    =JOIN(COLLECT({Sheet 1 Range 1}, {Sheet 1 Range 2}, Criteria1, {Sheet 1 Range 3}, Criteria2)) + " " + JOIN(COLLECT({Sheet 2 Range 1}, {Sheet 2 Range 2}, Criteria1, {Sheet 2 Range 3}, Criteria 2))

  • This came in handy for me today. Thanks for posting what Tech Support came up with.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!