Conditional Index Match
Hey y'all,
I have a formula that does an index match to a separate sheet however, there are no unique ids that I could match. I have narrowed down the data to a point where if I could add just one condition to the formula I could get the information I need. The source sheet has thousands of rows and the target sheet has about 600 so I'd rather not have to setup unique IDs. The employee unique ids typically work but since the source sheet may have multiple entries for an employee the formula stops at the first occurrence. The formula works if I sort the source sheet the right way but I can't always ensure that it stays sorted that way. The current formula is =INDEX({Uniform Order Tracking- CS Jacket Style}, MATCH(EID@row, {Uniform Order Tracking- CS EID}, 0)) but I only want it to pull in the info to the target sheet if the "Order Type" column (source sheet) is "Jacket Order"
Best Answer
-
You will need an INDEX/COLLECT instead.
=INDEX(COLLECT({Uniform Order Tracking- CS Jacket Style}, {Uniform Order Tracking- CS EID}, EID@row, {Source Sheet Order Type}, "Jacket Order"), 1)
Answers
-
You will need an INDEX/COLLECT instead.
=INDEX(COLLECT({Uniform Order Tracking- CS Jacket Style}, {Uniform Order Tracking- CS EID}, EID@row, {Source Sheet Order Type}, "Jacket Order"), 1)
-
Thanks Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!