CODEWITHSUNDEEP
if-statementexcel-formulanestedarguments
LiamL
LiamL

Reputation: 21

Excel formula has too many arguments

I created the below formula to run of a series of customer numbers saved in text format.

=IFERROR(IF(AND((LEFT($J3,3)="028"),$N3=11),"NI Landline",IF(AND((LEFT($J3,2)="07"),$N3=11),"Mobile","Other Number")),"Other Number")

Breakdown:

=IFERROR(
    IF(AND((LEFT($J3,3)="028"),$N3=11),
        "NI Landline",
    IF(AND((LEFT($J3,2)="07"),$N3=11),
        "Mobile",
    "Other Number")),
"Other Number")

This formula works fine but I needed to amend it slightly to differentiate the numbers a bit further, so I amended it to the below:

=IFERROR(IF(AND((LEFT($J2,3)="028"),$N2=11),"NI Landline",IF(AND((LEFT($J2,2)="07"),$N2=11),"UK Mobile",IF(AND((LEFT($J2,5)="00353"),$N2=14),"ROI Number","Other Number")),"Other Number")

Breakdown:

=IFERROR(
    IF(AND((LEFT($J2,3)="028"),$N2=11),
        "NI Landline",
    IF(AND((LEFT($J2,2)="07"),$N2=11),
        "UK Mobile",
    IF(AND((LEFT($J2,5)="00353"),$N2=14),
        "ROI Number",
    "Other Number")),
="Other Number")

Thinking I've replicated the conditions from the first 'IF' sections, I ran the formula and it returned 'too many arguments'. I've removed the new section so that it is the same as the first formula, and it works fine. I've checked the parentheses but the number matches on both sides. Any ideas?

I'm hoping it is something stupid, any assistance would be greatly appreciated! Thanks Liam

Upvotes: 1

Views: 2593

Answers (2)

p._phidot_
p._phidot_

Reputation: 1925

"too many arguments" means the number of "," , , "(" & ")" is not right. Just double check for each if(a,b,c) iferror(a,b), and and(a,b,c) to have enough brackets, data/arguments and comma. That should be sufficient.

Note: @Solar Mike had shared a great solution though. (:

Upvotes: 0

Solar Mike
Solar Mike

Reputation: 8375

you seem to be closing the brackets early for the and() statements : try removing the close brackets after 028" and 07" and 353"

So did a bit for the first two as per :

IF(AND(LEFT($J2,3)="028",$N2=11),"NI Landline",IF(AND((LEFT($J2,2)="07"),$N2=11),"UK Mobile","check"))

You should be able to expand from here.

Image to show the example : enter image description here

Upvotes: 1

Related Questions