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.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 467 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 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!