Vlookup help please

[ferde]

I'm not sure how to approach this and would appreciate some direction. Can I
use an IF formula with Vlookup?


A
1 Scores
2 150
3 100
4 140
5 120
6 200
First I need to calculate the difference between two numbers in column A.
For example the difference between A6 and A5.
Once I have determined this value I thought I would use a Vlookup table to
assign a grade in column B but I'm not certain if this will work because
sometimes the difference between the two numbers may be a negative number.

For Example:
If the value falls 20-30 points I can assign a Grade of 2
If the value falls 31-40 points I can assign a Grade of 3
If the value is changes < 20 points in either direction I can assign a Grade
of 0
If the value between two cells rises 20-30 points I can assign a Grade of 2
If the value between two cells rises 31-40 points I can assign a Grade of 3
If the value between two cells rises > 41 points I can assign a Grade of 4

Thank you in advance

 

[Jacob Skaria]

Try this

=LOOKUP(ABS(your formula here),{0,20,31,41},{0,1,2,3,4})

If this post helps click Yes

 

[ferde]

I'm not sure what formula to plug in to capture the difference between A4 and
A5

=LOOKUP(ABS(A4-A5),{0,20,31,41},{0,1,2,3,4}) =3

The formula I used is A4-A5 which equals a fall of 50 points and so the
Grade assigned should be 4. The next score added to cell A6 may be higher
than A5 and so I will need to subtract the higher score (A6)from the previous
score (A5) to determine the difference and assign a grade. Thank you for
your help.


A
Scores Grade
4 150 falls20-30 2
5 100 falls31-40 3
6 falls>40 4
0-19 0
rises20-29 1
rises30-40 2
rises>40 3