If with vlookup

[castlesmith]

This is what i am working on
=IF(I4<40,"U",VLOOKUP(I4,$AE$1:$AF$61,2,FALSE))

How i want it is if the Number is 40 below they get a "U"

and thing over it uses the vlook up

100 A
99 A
98 A
97 A
96 A
95 A
94 A
93 A
92 A
91 A
90 A
89 A
88 A
87 A
86 A
85 A
84 A
83 A
82 A
81 A
80 A
79 B
78 B
77 B
76 B
75 B
74 B
73 B
72 B
71 B
70 B
69 C
68 C
67 C
66 C
65 C
64 C
63 C
62 C
61 C
60 C
59 D
58 D
57 D
56 D
55 D
54 D
53 D
52 D
51 D
50 D
49 E
48 E
47 E
46 E
45 E
44 E
43 E
42 E
41 E
40 E

Will this work because i am getting an #N/A error in some cells

Can anyone help please.......

[berlan]

Instead try this formula,

=LOOKUP(I4,{0,40,50,60,70,80},{"U","E","D","C","B","A"})

Hope I got the intervals correct.

[Special-K]

The value in I4 does not exist in the range AE1:AF61

[berlan]

Special-K, that's true,

you could change

=IF(I4<40,"U",VLOOKUP(I4,$AE$1:$AF$61,2,FALSE))

to

=IF(I4<40,"U",VLOOKUP(I4,$AE$1:$AF$61,2,TRUE))

if you sort the data in col AE from smallest to largest value. Then it would work similarly to the lookup formula I posted above.

Another alternative,

=VLOOKUP(I4,{0,"U";40,"E";50,"D";60,"C";70,"B";80,"A"},2,TRUE)

Cheers,
berlan