# Nested IF or Vlookup?

1. ## Nested IF or Vlookup?

I am trying to take a number and fit it in a group and multiply all preceding groups and the existing group by a multiplier and all other numbers in the table be 0.

For instance if my number is 313 then I will have 150 in the summary field for that row and 350 in the next and then I will take the minimum of 201 which is 112 and multiply it by 1.35 and the next two results will be zero.

6-19-2013 7-31-55 AM.jpg

this is the formula I was using where column H is the first column you see.

``Please Login or Register  to view this content.``
Any help would be appreciated as I don't do this often and I had no success with the Vlookup but I think it might be able to work with that formula as well.

Jeremy

2. ## Re: Nested IF or Vlookup?

Try

=SUMPRODUCT(--(A1>{0,75,200,500,1000,9999}), (A1-{0,75,200,500,1000,9999}), {0,2,-0.25,-0.40,-0.35,-0.75})

http://www.mcgimpsey.com/excel/variablerate.html

3. ## Re: Nested IF or Vlookup?

I can't seem to make this work. I am attaching a sample of the file so you see my problem. I want to compare \$c\$9 to Column H and Column I. If the number is higher than column j then I want to multiply the amount of \$C\$9 by the value in column Q by K. If the number falls in the range of H to I then I want to multiply the amount over H by column K. All other rows should return a zero.

Hope this gets me a bit closer to a solution.

savings calc.xlsx

4. ## Re: Nested IF or Vlookup?

In N3

=MIN(J3,\$C\$9)*(\$C\$9>=H3)*K3

Copy down

5. ## Re: Nested IF or Vlookup?

Jeremy, try the following formula: =IF(AND(C7/12>=\$H\$3,C7/12<=\$I\$3),C7/12*\$K\$3,IF(AND(C7/12>=\$H\$4,C7/12<=\$I\$4),C7/12*K4,"")) IF this works, you will need to continue inserting "if"s were the ,"" starts.

6. ## Re: Nested IF or Vlookup?

This got me close and with a modification it did the job, Thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)