Nested IF / VLOOKUP?

[tim]

All,
I'm trying to put together an application that will determine how
much more in sales are needed to move to the nest bracket. I need to do
this for 12 mos. I understand that Nested IF statements will not work.
From the research I have done, I see that VLOOKUP may work, but I
really don't understand the function. Below is what I'm trying to
accomplish with the Nested Loop. The cell reference is the dollar amt
of sales for the month. All help is truly appreciated.

tks

Tim


=IF(C33<150,150-C33,IF(C33<250,250-C33,IF(C33<500,500-C33,IF(C$33<750,75
0-C33,IF(C33<1000,1000-C33,IF(C33<1500,1500-C33,IF(C33<2000,2000-C33,IF(
C33<3000,3000-C33))))))))

 

[Bob Phillips]

Tim,

Assuming that the values 150, 250, 500, etc are in M1:M10, try

=INDEX(M1:M10,MATCH(C33,M1:M10)+1)-C33

 

[tim]

In article <070920030922469531%[email protected]>, tim
In a partial answer to my own question, if current monthy sales are
$1750.00, VLOOKUP will return a value of 1500, which is bracket 7. I
need the equation to choose bracket 8. Then I can return the amt of
sales needed to reach bracket 8.

How do I get the equation to return the proper number?

 

[J.E. McGimpsey]

One way:

Put your bracket numbers in a list, starting with 0, e.g.:

A1: 0
A2: 150
A3: 250
....
A9: 3000

Then you can use:

=INDEX(A:A,MATCH(C33,A:A)+1,TRUE)-C33

 

[tim]

thanks!
That was the fix I needed!


tks again


tim