Too many arguments in IF function

[smscheung]

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!

 

[Peter_SSs]

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]

Re: Too many arguements in IF function

That's it! Thanks a lot

 

[mohadin]

Re: Too many arguements in IF function

Deleted Repay

 

[Peter_SSs]

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]

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]

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]

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!