Formula Help

Ronald Anderson

I am getting an incorrect argument error with the below formula.

I am trying to collect data from different sheets based on whether the cell value in the Airside / Landside column is Airside or Landside.

=IFERROR(IF([Airside / Landside]@row = "Airside", INDEX(COLLECT({EqNo}, {Employee ID}, [Employee #]@row), 1), IF([Airside / Landside]@row = "Landside", INDEX(COLLECT({LSEQNo}, {LS Employee Number}, [Employee #]@row), 1),"Employee Not Listed")

Thanks!

Paul Newcome

Try inserting a second and third closing parenthesis after the final INDEX/COLLECT.

[Employee #]@row), 1))),"Employee Not Listed")

Jeff Reisman

@Ronald Anderson

The incorrect argument set error is being thrown because of syntax and a missing argument.

Let's start with the nested IFs. You have two IF statements, so for sure at the end of your IFs you'll need two end parentheses, because you have to close out both IFs. So to start with, add a second end parentheses after "Employee Not Listed")

Next, you have your IFERROR. The syntax for IFERROR is =IFERROR(value/formula, value if there's an error). Right now you just just have the =IFERROR(value/formula) part, so you're missing the value if there's an error. If you want "Employee Not Listed" to be the value if error, try closing off your two IFs before that value (see bold end parentheses):

=IFERROR(IF([Airside / Landside]@row = "Airside", INDEX(COLLECT({EqNo}, {Employee ID}, [Employee #]@row), 1), IF([Airside / Landside]@row = "Landside", INDEX(COLLECT({LSEQNo}, {LS Employee Number}, [Employee #]@row), 1)),"Employee Not Listed")

If you want some other value for the error condition, close the IFs after "Employee Not Listed" and specify that other value afterward:

=IFERROR(IF([Airside / Landside]@row = "Airside", INDEX(COLLECT({EqNo}, {Employee ID}, [Employee #]@row), 1), IF([Airside / Landside]@row = "Landside", INDEX(COLLECT({LSEQNo}, {LS Employee Number}, [Employee #]@row), 1),"Employee Not Listed")), "Value if Error")

Paul Newcome

Try inserting a second and third closing parenthesis after the final INDEX/COLLECT.

[Employee #]@row), 1))),"Employee Not Listed")

Jeff Reisman

@Ronald Anderson

The incorrect argument set error is being thrown because of syntax and a missing argument.

Let's start with the nested IFs. You have two IF statements, so for sure at the end of your IFs you'll need two end parentheses, because you have to close out both IFs. So to start with, add a second end parentheses after "Employee Not Listed")

Next, you have your IFERROR. The syntax for IFERROR is =IFERROR(value/formula, value if there's an error). Right now you just just have the =IFERROR(value/formula) part, so you're missing the value if there's an error. If you want "Employee Not Listed" to be the value if error, try closing off your two IFs before that value (see bold end parentheses):

=IFERROR(IF([Airside / Landside]@row = "Airside", INDEX(COLLECT({EqNo}, {Employee ID}, [Employee #]@row), 1), IF([Airside / Landside]@row = "Landside", INDEX(COLLECT({LSEQNo}, {LS Employee Number}, [Employee #]@row), 1)),"Employee Not Listed")

If you want some other value for the error condition, close the IFs after "Employee Not Listed" and specify that other value afterward:

=IFERROR(IF([Airside / Landside]@row = "Airside", INDEX(COLLECT({EqNo}, {Employee ID}, [Employee #]@row), 1), IF([Airside / Landside]@row = "Landside", INDEX(COLLECT({LSEQNo}, {LS Employee Number}, [Employee #]@row), 1),"Employee Not Listed")), "Value if Error")

Ronald Anderson

Thank you both for your helpful and quick response!

Paul Newcome

Happy to help. 👍️