How to use index, collect, contains
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
-
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))
-
That is a great little workaround. Thank you!
-
Is there a way however to use the formula instead of doing the workaround?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!