# Too many arguments in IF function

#### smscheung

##### New Member
It is saying there are too many arguements, where should I add AND OR in order for this formula to work?

=IF(\$D\$1="","",VLOOKUP(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$DQ,91,FALSE)="","",'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$DQ,91,FALSE))

Thanks!

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Peter_SSs

##### MrExcel MVP, Moderator
Re: Too many arguements in IF function

.. where should I add AND OR in order for this formula to work?
That depends on exactly what the formula is trying to achieve. My best guess is this.

=IF(\$D\$1="","",IF(VLOOKUP(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$DQ,91,FALSE)="","",VLOOKLUP(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$DQ,91,FALSE)))

#### smscheung

##### New Member
Re: Too many arguements in IF function

That's it! Thanks a lot

##### Active Member
Re: Too many arguements in IF function

Deleted Repay

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator

Re: Too many arguements in IF function

That's it! Thanks a lot
You're welcome.

One further comment. I don't know if the data in your 'Staff info summary' sheet changes much but if any cell in columns B:DQ changes (that is over 120 million cells) that formula (& any other similar ones) will be flagged to recalculate. That could possibly lead to a poor sheet performance issue.

Since that formula is actually only looking at columns B and CN of the Staff sheet, this formula (untested) should do the same job and it would only be flagged to recalculate if a cell in column B or column CN (ie just over 2 million cells) changes.

=IF(\$D\$1="","",IF(INDEX('[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$CN:\$CN,MATCH(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$B,FALSE))="","",INDEX('[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$CN:\$CN,MATCH(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$B,FALSE))))

#### smscheung

##### New Member
Re: Too many arguements in IF function

That depends on exactly what the formula is trying to achieve. My best guess is this.

=IF(\$D\$1="","",IF(VLOOKUP(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$DQ,91,FALSE)="","",VLOOKLUP(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$DQ,91,FALSE)))

Just tested this formula, the result came out #NAME ? , is there something else I need to add?

#### Peter_SSs

##### MrExcel MVP, Moderator
Re: Too many arguements in IF function

Just tested this formula, the result came out #NAME ? , is there something else I need to add?
No, mine was untested - because i don't have the other workbook set up & I made a typo. Leave out this errant letter & try again.
That depends on exactly what the formula is trying to achieve. My best guess is this.

=IF(\$D\$1="","",IF(VLOOKUP(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$DQ,91,FALSE)="","",VLOOKLUP(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$DQ,91,FALSE)))

#### smscheung

##### New Member
Re: Too many arguements in IF function

You're welcome.

One further comment. I don't know if the data in your 'Staff info summary' sheet changes much but if any cell in columns B:DQ changes (that is over 120 million cells) that formula (& any other similar ones) will be flagged to recalculate. That could possibly lead to a poor sheet performance issue.

Since that formula is actually only looking at columns B and CN of the Staff sheet, this formula (untested) should do the same job and it would only be flagged to recalculate if a cell in column B or column CN (ie just over 2 million cells) changes.

=IF(\$D\$1="","",IF(INDEX('[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$CN:\$CN,MATCH(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$B,FALSE))="","",INDEX('[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$CN:\$CN,MATCH(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info summary'!\$B:\$B,FALSE))))

This one seems work better, I will try to use this first, thanks!

Replies
1
Views
46
Replies
2
Views
259
Replies
17
Views
302
Replies
3
Views
68
Replies
1
Views
39