Error when using nested IF

[AdRock]

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

=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),
 
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),"NOT FOUND"))))))

 

[Gerald Higgins]

There's a number of small problems with this formula

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

=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 ?

 

[AdRock]

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"

=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

=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

 

[Gerald Higgins]

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.

=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,"???"))))