You've entered too many arguments for this function

[shadi]

Excel says: You've entered too many arguments for this function:

=IF(H11=0,0,IF(D11="Copper",VLOOKUP($H11,'Bore Area'!$A$4:$D$17,2),IF(D11="Medium Steel",VLOOKUP($H11,'Bore Area'!$A$4:$D$17,4)),VLOOKUP($H11,'Bore Area'!$A$18:$F24,6)))

I have a look up table with pipe diameter in the left side, which is input for H11 on the main sheet, and with the related pipe type find the bore area. On the 2,4 and 6 columns of the lookup table, are the Bore Area which should be read with the VLookup.

Any suggestion?

 

[Aladin Akyurek]

Excel says: You've entered too many arguments for this function:

=IF(H11=0,0,IF(D11="Copper",VLOOKUP($H11,'Bore Area'!$A$4:$D$17,2),IF(D11="Medium Steel",VLOOKUP($H11,'Bore Area'!$A$4:$D$17,4)),VLOOKUP($H11,'Bore Area'!$A$18:$F24,6)))

I have a look up table with pipe diameter in the left side, which is input for H11 on the main sheet, and with the related pipe type find the bore area. On the 2,4 and 6 columns of the lookup table, are the Bore Area which should be read with the VLookup.

Any suggestion?

Looks like...

=IF(H11=0,0,
    IF(D11="Copper",
        VLOOKUP($H11,'Bore Area'!$A$4:$D$17,2),
        IF(D11="Medium Steel",
          VLOOKUP($H11,'Bore Area'!$A$4:$D$17,4),
          VLOOKUP($H11,'Bore Area'!$A$18:$F24,6))))

 

[shadi]

Thats same as mine, that seems to be too long. Excel says: You've entered too many arguments for this function.

 

[snowblizz]

Thats same as mine, that seems to be too long. Excel says: You've entered too many arguments for this function.

You have a typo in your formula. Aladin's formula works if I test it, ie no error on entering, whereas your formula gives the error.
There's a parenthesis before the last vlookup that should probably be at end, like in Aladin's formula.

 

[shadi]

Thanks, that worked. I didn't understand what was wrong with my formula though!

 

[Aladin Akyurek]

Thanks, that worked. I didn't understand what was wrong with my formula though!

It was a misplaced parenthesis...

 

[Elle_H]

Hi - anyone available to review this and help out?

Column J has a set number. If the number is between a range, we need excel to return a value.

For example:
0-100 return q35
101-125 return Q36

We originally did it and it said we had too many arguments – we’ve been messing around now and are completely lost
=IF(J2<100.1,"Q35",IF(J2>100.9,J2<125.1,"Q36",IF(J2>125.9,J2<150.1,"Q37",IF(J2>150.9,J2<175.1,"Q38",IF(J2>175.9,J2<200.1,"Q39",IF(J2>200.9,J2<250.1,"Q45",IF(J2>250.9,"Q15",0)))))))

 

[Jonmo1]

You should probably start your own thread.
But I'd suggest looking into a Lookup formula.
Build a table with the low end of each range in 1 column, and the corresponding value you want returned for each range in the column to the right of it.
Then you can use that lookup table with a lookup formula.

 

[paolo03]

I have similar trouble with this function
=IF(AND(G2>=83,H2>=50),x!$A$2,IF(AND(G2<83,G2>=67,H2>=50),x!$A$3,IF(AND(G2<67,G2>=50,H2>=50),x!$A$4,IF(AND(G2>=83,H2<50),x!$A$5,IF(AND(G2<83,G2>=67,H2<50),x!$A$6,if(and(G2<67,G2>=50,H2<50),x!$A$7,if(and(G2<50,g2>=34,H2>=50),x!$A$8,IF(AND(G2<34,G2>=17,H2>=50),x!$A$9,IF(AND(G2<17,H2>=50),x!$A$10),if(and(G2<50,g2>=34,H2<50),x!$A$11,IF(AND(G2<34,G2>=17,H2<50),x!$A$12,x!$A$13)))))))))) , is this function is too long or is it typo error?
I cant find the problem

 

[MrIfOnly]

Fixed:

=IF(AND(G2>=83,H2>=50),x!$A$2,IF(AND(G2<83,G2>=67,H2>=50),x!$A$3,IF(AND(G2<67,G2>=50,H2>=50),x!$A$4,IF(AND(G2>=83,H2<50),x!$A$5,IF(AND(G2<83,G2>=67,H2<50),x!$A$6,IF(AND(G2<67,G2>=50,H2<50),x!$A$7,IF(AND(G2<50,G2>=34,H2>=50),x!$A$8,IF(AND(G2<34,G2>=17,H2>=50),x!$A$9,IF(AND(G2<17,H2>=50),x!$A$10,IF(AND(G2<50,G2>=34,H2<50),x!$A$11,IF(AND(G2<34,G2>=17,H2<50),x!$A$12,x!$A$13)))))))))))

The "you've entered too many arguments for this function" error is not referring to the length of the function, but, rather, how many arguments were used in the formula. For example: the formula =IF(A1="apples","yes","no","maybe") will return this error because I entered three arguments for what the IF should return when there should only be two (one for true, one for false).

A couple of ways to troubleshoot long formulas like this:
1) Break the formula down into its components, like this:
=IF(AND(G2>=83,H2>=50),
x!$A$2,
IF(AND(G2<83,G2>=67,H2>=50),
x!$A$3,
IF(AND(G2<67,G2>=50,H2>=50),
x!$A$4,
IF(AND(G2>=83,H2<50),
x!$A$5,
IF(AND(G2<83,G2>=67,H2<50),
x!$A$6,
if(and(G2<67,G2>=50,H2<50),
x!$A$7,
if(and(G2<50,g2>=34,H2>=50),
x!$A$8,
IF(AND(G2<34,G2>=17,H2>=50),
x!$A$9,
IF(AND(G2<17,H2>=50),
x!$A$10),
if(and(G2<50,g2>=34,H2<50),
x!$A$11,
IF(AND(G2<34,G2>=17,H2<50),
x!$A$12,
x!$A$13))))))))))


This make it more readable and much easier to spot errors like the extra parenthesis (highlighted in red) in your formula.

2) While you're in the cell with the formula, click on each function in the formula then click the Fx button. This will open a dialog breaking the formula down for that function and show results and errors.

Regards,

CJ