How to use index, collect, contains

AlexCline

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!

dojones

Create a helper column on the PO sheet to extract the 12345 from the PO number. Then Index Match to the helper column

=VALUE(IFERROR(LEFT(PO@row, FIND("-", PO@row) - 1), PO@row))

AlexCline

That is a great little workaround. Thank you!

AlexCline

Is there a way however to use the formula instead of doing the workaround?