Can someone tell me why this function isn't working?

=IF($E$35=2, IF($F$35<$F$41, 0, IF($F$35>$F$79, 10500, VLOOKUP($F$35, $F$41:$H$79, 2, FALSE)))), IF($E$35=1, IF($F$35<$F$41, 0, IF($F$35>$F$79, 3900, VLOOKUP($F$35, $F$41:$H$79, 3, FALSE)))) I al...

Formulas and Functions

Riny_van_Eekelen
Nov 02, 2023
DianaC27 You had a parenthesis in the wrong place. The following formula does not give the #VALUE error, though I can not judge if the result is correct as you provided no test data.
=IF($E$35=2,IF($F$35<$F$41,0,IF($F$35>$F$79,10500,VLOOKUP($F$35,$F$41:$H$79,2,FALSE))),IF($E$35=1,IF($F$35<$F$41,0,IF($F$35>$F$79,3900,VLOOKUP($F$35,$F$41:$H$79,3,FALSE)))))

The red parenthesis should move all the way to the end.
=IF($E$35=2, IF($F$35<$F$41, 0, IF($F$35>$F$79, 10500, VLOOKUP($F$35, $F$41:$H$79, 2, FALSE)))), IF($E$35=1, IF($F$35<$F$41, 0, IF($F$35>$F$79, 3900, VLOOKUP($F$35, $F$41:$H$79, 3, FALSE))))

DianaC27

Copper Contributor

Nov 02, 2023

Thank you! I knew all the elements were good, but I always have trouble with the syntax. 😕

Riny_van_Eekelen

Platinum Contributor

Nov 02, 2023

DianaC27 Not easy with nested IF functions. Best to avoid them if you can!

mathetes
Gold Contributor
Nov 02, 2023
DianaC27

Riny_van_Eekelen wrote: Not easy with nested IF functions. Best to avoid them if you can!

One way to avoid nesting multiple levels of IF, a way that is often effective, is to use the IFS function.

Another way, especially if there is the possibility of conditions changing, rather than hard-coding the conditions into the formula, is to use a table and look up the resulting values with one of the many ways to do that. You already have some VLOOKUP in your formula, suggesting to me that this might be the more effective route for you.

Or some combination of the above.

Forum Discussion

Can someone tell me why this function isn't working?

Resources