Forum Discussion
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 already checked my data elements for extra spaces and made sure all of them were in a number format, but I'm still getting the #VALUE! error with the message "A value used in the formula is of the wrong data type." Please assist. Thanks!
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))))
4 Replies
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))))
- Thank you! I knew all the elements were good, but I always have trouble with the syntax. 😕
DianaC27 Not easy with nested IF functions. Best to avoid them if you can!
