Too many arguments in IF function

smscheung

New Member
Joined
Mar 14, 2019
Messages
26
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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)))
 
Upvote 0
Re: Too many arguements in IF function

Deleted Repay
 
Last edited:
Upvote 0
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))))
 
Upvote 0
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?
 
Upvote 0
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. :oops:
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)))
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top