What am I doing wrong in this formula?

Casie

=IFERROR(INDEX(COLLECT({Master Unit/Manager List WI Range 1}, {Master Unit/Manager List WI Range 2}, [Site]@row, {Master Unit/Manager List WI Range 3}, [Department Name]@row),[{Unit/Manager List WI Range 4}))

My goal with this formula is: if SITE and DEPARTMENT Name on Sheet1 matches SITE NAME and DEPARTMENT NAME on sheet 2 then pull the Manager Email from sheet 2 into Manager in sheet 1

AW Placement Request is sheet 1 and Unit/Manger List WI is sheet 2

Jason Albrecht

Hi @Casie

All good. Now let's have a look at the formula.

According to this article, we want the formula to have the following syntax:

=INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1)

Ignoring the IFERROR for a moment, we'll need to edit your formula to something like (note the '1' at the end):

=INDEX(COLLECT({Master Unit/Manager List WI Range 1}, {Master Unit/Manager List WI Range 2}, [Site]@row, {Master Unit/Manager List WI Range 3}, [Department Name]@row),1)

Now to add the IFERROR function

=IFERROR(INDEX(COLLECT({Master Unit/Manager List WI Range 1}, {Master Unit/Manager List WI Range 2}, [Site]@row, {Master Unit/Manager List WI Range 3}, [Department Name]@row),1), {Unit/Manager List WI Range 4})

Lets see how that goes...

Jason Albrecht

Hi @Casie

I have not looked at the formula in detail yet. But, what I can see is a mismatch of brackets.

There seems to be an open square bracket "[" (red arrow), which doesn't seem necessary?

And, while there's 3 open brackets "(", there are 2 closed brackets ")"?

I suspect if the square bracket is deleted and an extra closed bracket included at the end, you may get your answer?

Let us know how you go.

Casie

I get the #incorrect argument error.

Thank you I've been looking at this for hours

Jason Albrecht

Hi @Casie

All good. Now let's have a look at the formula.

According to this article, we want the formula to have the following syntax:

=INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1)

Ignoring the IFERROR for a moment, we'll need to edit your formula to something like (note the '1' at the end):

=INDEX(COLLECT({Master Unit/Manager List WI Range 1}, {Master Unit/Manager List WI Range 2}, [Site]@row, {Master Unit/Manager List WI Range 3}, [Department Name]@row),1)

Now to add the IFERROR function

=IFERROR(INDEX(COLLECT({Master Unit/Manager List WI Range 1}, {Master Unit/Manager List WI Range 2}, [Site]@row, {Master Unit/Manager List WI Range 3}, [Department Name]@row),1), {Unit/Manager List WI Range 4})

Lets see how that goes...

Casie

@Jason Albrecht when looking at =INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1) I found that I had things backwards. I ended up using =INDEX(COLLECT({Unit/Manager List WI Range 1}, {Unit/Manager List WI Range 2}, Site@row, {Unit/Manager List WI Range 3}, [Department Name]@row), 1) and it worked. Minus I am getting #INVALID VALUE when there is no match

Jason Albrecht

Awesome @Casie

Thank you for letting us know.

Glad to be of service.