Help with Join collect multi criteria error

I need help with my formula and am not getting anywhere and keep receiving an error in the test. The formula should find the cell needed in another sheet (cross sheet reference) that has the matching ID at row (and ID row of the 2nd Sheet) but also has either "Year End Planning" or " Year- End Planning" in a separate column on the 2nd sheet. My current formula working only gives me the cells when it has
"Year End Planning" but does not give me anything if it has "Year-End Planning" the details needed of this:
=IFERROR(INDEX(COLLECT({Sheet - QER Assembly}, {Sheet - QER Client ID FX}, [Client Number]@row, {Sheet - QER Workflow Des}, "Year End Planning"), 1), " ")
(Also send sheet has data uploaded through automation daily so I cannot change the Year-End to Year End as a solution.)
This is the closest I have gotten to the solution but still getting an error message.
=IFERROR(JOIN(COLLECT({Sheet - QER Assembly}, [Client Number]@row, {Sheet - QER Client ID FX}, {Sheet - QER Workflow Des}, OR(@cell = "Year End Planning", @cell = "Year-End Planning")), ", "), " ")
or
=IFERROR(JOIN(COLLECT
({Sheet - QER Assembly}, [Client Number]@row, {Sheet - QER Client ID FX}, {Sheet - QER Workflow Des}, OR(@cell = "Year End Planning", @cell = "Year-End Planning"))), " ")
Thanks in advance to anyone that can offer some help or guidance.
Best Answer
-
Hi @NCookBT83,
I think your syntax is off. It is correct in the formula you say is working, but when you added the second criteria, it looks like you reversed things in the COLLECT portion.
See if this works.
=IFERROR(JOIN(COLLECT({Sheet - QER Assembly}, {Sheet - QER Client ID FX}, [Client Number]@row, {Sheet - QER Workflow Des}, OR(@cell = "Year End Planning", @cell = "Year-End Planning")), ", "), " ")
*Untested
Hope this helps,
Dave
Answers
-
Hi @NCookBT83,
I think your syntax is off. It is correct in the formula you say is working, but when you added the second criteria, it looks like you reversed things in the COLLECT portion.
See if this works.
=IFERROR(JOIN(COLLECT({Sheet - QER Assembly}, {Sheet - QER Client ID FX}, [Client Number]@row, {Sheet - QER Workflow Des}, OR(@cell = "Year End Planning", @cell = "Year-End Planning")), ", "), " ")
*Untested
Hope this helps,
Dave
-
That worked!!! Thank you so much for catching that!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!