# Error when using nested IF

##### Board Regular
hello

I am having a problem using nested IFs.

The problem occurs on the last IF where the MATCH is.

I have tested each if separately and they all work as expected but when i try to group them, i get the error "The formula you typed contains an error:" and it points to this.

IF(AND(\$G4="2D",\$F4="Qtr"),sum((VLOOKUP(\$C4,'Economy Variance'!\$D\$4:\$P\$10,MATCH(\$F4,'Economy Variance'!\$E\$3:\$P\$3)+1,FALSE))*\$E4)

Is there something missing that woudl cause the error?

any help appreciated

Code:
=IF(AND(\$G4="ND",\$F4="Full"),VLOOKUP(\$C4,'Next Day Variance'!\$D\$4:\$P\$10,MATCH(\$E4,'Next Day Variance'!\$E\$3:\$P\$3)+1,FALSE),

IF(AND(\$G4="ND",\$F4="Half"),sum((VLOOKUP(\$C4,'Next Day Variance'!\$D\$4:\$P\$10,MATCH(\$F4,'Next Day Variance'!\$E\$3:\$P\$3)+1,FALSE))*\$E4),

IF(AND(\$G4="ND",\$F4="Qtr"),sum((VLOOKUP(\$C4,'Next Day Variance'!\$D\$4:\$P\$10,MATCH(\$F4,'Next Day Variance'!\$E\$3:\$P\$3)+1,FALSE))*\$E4),

IF(AND(\$G4="2D",\$F4="Full"),VLOOKUP(\$C4,'Economy Variance'!\$D\$4:\$P\$10,MATCH(\$E4,'Economy Variance'!\$E\$3:\$P\$3)+1,FALSE),

IF(AND(\$G4="2D",\$F4="Half"),sum((VLOOKUP(\$C4,'Economy Variance'!\$D\$4:\$P\$10,MATCH(\$F4,'Economy Variance'!\$E\$3:\$P\$3)+1,FALSE))*\$E4),

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
There's a number of small problems with this formula
Rich (BB code):
IF(AND(\$G4="2D",\$F4="Qtr"),sum((VLOOKUP(\$C4,'Economy Variance'!\$D\$4:\$P\$10,
MATCH(\$F4,'Economy Variance'!\$E\$3:\$P\$3)+1,FALSE))*\$E4)

1) The Sum part serves no useful purpose as far as I can tell, and can be left out.
2) It is probably better (but not essential) to include a third argument for the MATCH function, the "match type", for example
Rich (BB code):
=MATCH(F4,E3:P3,0)
3) The formula seems to be missing at least one closing bracket ")" character.
4) The formula seems to not include a FALSE argument for the IF function. Is there more to this formula that has been missed off your post ?

I've been working on some more of this and i'm getting closer

I need the sum on the vlookup becuase i'm multiplying the result by the value in another cell to give a correct figure.

I've tried this function and it works for any cells in column G that match ND no matter if column F is "Full", "Half" or "Qtr"

Code:
=IF(AND(\$G43="ND",\$F43="Full"),VLOOKUP(\$C43,'Next Day Variance'!\$D\$4:\$P\$10,MATCH(\$E43,'Next Day Variance'!\$E\$3:\$P\$3,0)+1),IF(AND(\$G43="ND",OR(\$F43="Qtr",\$F43="Half")),SUM(VLOOKUP(\$C43,'Next Day Variance'!\$D\$4:\$F\$10,MATCH(\$F43,'Next Day Variance'!\$E\$3:\$F\$3,0)+1))*E43))

if i try to nest that function creating 4 nested IFs, I get "You've entered too many arguments for this function"

This is my complete function
Code:
=IF(AND(\$G43="ND",\$F43="Full"),VLOOKUP(\$C43,'Next Day Variance'!\$D\$4:\$P\$10,MATCH(\$E43,'Next Day Variance'!\$E\$3:\$P\$3,0)+1),IF(AND(\$G43="ND",OR(\$F43="Qtr",\$F43="Half")),SUM(VLOOKUP(\$C43,'Next Day Variance'!\$D\$4:\$F\$10,MATCH(\$F43,'Next Day Variance'!\$E\$3:\$F\$3,0)+1))*E43),IF(AND(\$G43="2D",\$F43="Full"),VLOOKUP(\$C43,'Economy Variance'!\$D\$4:\$P\$10,MATCH(\$E43,'Economy Variance'!\$E\$3:\$P\$3,0)+1),IF(AND(\$G43="2D",OR(\$F43="Qtr",\$F43="Half")),SUM(VLOOKUP(\$C43,'Economy Variance'!\$D\$4:\$F\$10,MATCH(\$F43,'Economy Variance'!\$E\$3:\$F\$3,0)+1))*E43))))

Basically it looks up 2 values in my table and based on what's in my table it will perform the correct 2D VLOOKUP.

My combination are:

ND Full
ND Half
ND Qtr
2D Full
2D Half
2D Qtr

Try this.

I don't know if it does what you want, because I don't know what your data is like, but it is a valid formula.

I've shuffled a few brackets, and deleted all the SUMs, I really don't think you need them. I've also added the FALSE argument for all your Vlookups, to search for an exact match - maybe you don't want that.
I've also added the false argument for your main IF statement, to show "???" if none of the conditions are met. Again, you might not want that, if it's possible that this argument will be required, you probably want to specify something different. On the other hand, if there is no possibility that this argument will be required, you can remove your final IF statement, and include its arguments as the FALSE argument for the previous IF statement.

Code:
=IF(AND(\$G43="ND",\$F43="Full"),
VLOOKUP(\$C43,'Next Day Variance'!\$D\$4:\$P\$10,MATCH(\$E43,
'Next Day Variance'!\$E\$3:\$P\$3,0)+1,FALSE),
IF(AND(\$G43="ND",OR(\$F43="Qtr",\$F43="Half")),
(VLOOKUP(\$C43,'Next Day Variance'!\$D\$4:\$F\$10,MATCH(\$F43,
'Next Day Variance'!\$E\$3:\$F\$3,0)+1,FALSE))*E43,
IF(AND(\$G43="2D",\$F43="Full"),
VLOOKUP(\$C43,'Economy Variance'!\$D\$4:\$P\$10,MATCH(\$E43,
'Economy Variance'!\$E\$3:\$P\$3,0)+1,FALSE),
IF(AND(\$G43="2D",OR(\$F43="Qtr",\$F43="Half")),
(VLOOKUP(\$C43,'Economy Variance'!\$D\$4:\$F\$10,MATCH(\$F43,
'Economy Variance'!\$E\$3:\$F\$3,0)+1))*E43,"???"))))

Replies
17
Views
1K
Replies
11
Views
463
Replies
4
Views
195
Replies
4
Views
154
Replies
1
Views
242

1,202,990
Messages
6,052,948
Members
444,620
Latest member
marialewis16

### 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.

### Which adblocker are you using?

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

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