How do you nest VLOOKUP in multiple IF functions?

K

Kevin

I am having a problem nesting vlookup functions in
multiple if functions. I believe the formula I want
should look like the following: =IF((VLOOKUP
("MN",G3:H25,2,FALSE)>700),0.2*(H3-700),IF((VLOOKUP
("S1",G3:H25,2,FALSE)>600),0.3*(H3-600),0)).

Here is what my spreadsheet looks like from cells H
through I. Under Overage Charge (I), is where the formula
is. The scenerio is that if actual milage in a week is
surpassed, a charge is given to that person in that
position (there are different persons in the same
position). That charge is different for the position of
the person. The second MN has given the desired result,
the first and third should say 0 because they didn't go
over. Can anyone help me with this?
G H I
Position Actual Milage Overage Charge
MN 391 -62.7
MN 1948 249.6
MN 303 -89.1
S1 851 #N/A
S1 759 #N/A
S1 0 #N/A
S1 0 #N/A
S1 1000 #N/A
 
A

Andy B

Hi

If I understand your post correctly, you don't need VLOOKUP(). As far as I
can tell, you are putting this formula in each Overage Charge row. Try this:
=IF(AND(G3="MN",H3>700),(H3-700)*0.2,IF(AND(G3="S1",H3>600),(H3-600)*0.3,0))
which you can fill down.
 
Top