Too many arguments in IF function

smscheung

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

Some videos you may like

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
Joined
May 28, 2005
Messages
46,227
Office Version
  1. 365
Platform
  1. Windows
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)))
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
490
Office Version
  1. 2013
Platform
  1. Windows
Re: Too many arguements in IF function

Deleted Repay
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,227
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 14, 2019
Messages
22
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
Joined
May 28, 2005
Messages
46,227
Office Version
  1. 365
Platform
  1. Windows
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)))
 

smscheung

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

Watch MrExcel Video

Forum statistics

Threads
1,108,952
Messages
5,525,851
Members
409,667
Latest member
jwieting

This Week's Hot Topics

Top