INDEX Match or INDEX Collect - formula error
Sheet 1: contains a list with Job #'s and info
Sheet 2: Contains data, with job #'s
I want to look up the matching "Job #" (Sheet 1) on Sheet 2 and then, if not on "Hold" (separate column), I want to pull in the cell data from a specific column named "Prep".
This is the basic formula that I am using and I am getting error "#Incorrect Argument Set"
=INDEX(COLLECT({Sheet 2 Prep Column}, {Sheet 2 Job # Column}, [Job #]@row, {Sheet 2 Hold Column}, <>"HOLD"))
Previously, my formula worked as this:
=INDEX({Sheet 2 Prep Column}, MATCH([Job #]@row, {Sheet 2 Job # Column}, 0))
BUT, it was only pulling in the first instance of the match, which was on hold. I want it to pull the cell data on the first "Non-Hold" instance.
Please help! ;)
Best Answer
-
You need to specify a row number in the INDEX function:
=INDEX(COLLECT({Sheet 2 Prep Column}, {Sheet 2 Job # Column}, [Job #]@row, {Sheet 2 Hold Column}, <>"HOLD"), 1)
Answers
-
You need to specify a row number in the INDEX function:
=INDEX(COLLECT({Sheet 2 Prep Column}, {Sheet 2 Job # Column}, [Job #]@row, {Sheet 2 Hold Column}, <>"HOLD"), 1)
-
PERFECT!! Thank you Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!