INDEX Match or INDEX Collect - formula error

Meredith Krueger

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! ;)

Paul Newcome

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)

Paul Newcome

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)

Meredith Krueger

PERFECT!! Thank you Paul!

Paul Newcome

Happy to help. 👍️