INDEX(MATCH) or JOIN(COLLECT)?

Jeremy_P

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? 

Jeremy_P

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