How to use index, collect, contains

Options

Hello,

I have 2 sheets: 1 is a "Sales Orders" and the other is "Purchase Orders". The PO# and SO# are almost identical, except that the PO# may or may not contain a "-A" or "-B" after the number. I would like to use those reference points to spit out the Customer Name listed in the sales order sheet. However, an index match formula only allows an exact match. \

For example, the SO# may be 12345 with customer AMERICA and the PO# is 12345-B. I want a formula that says, "if the PO# contains '12345' and matches the SO#, spit out 'AMERICA'.

please help!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!