lookup formula

[sachin483]

i am using the below lookup formula but it the value less than 0 it give error

=LOOKUP(F3,{0,80,90,110,150},{"Excellent","Good","Average","Below Average","Poor"})

[TABLE="width: 165"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]-1[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD]Excellent[/TD]
[/TR]
[TR]
[TD="align: right"]88[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD]Below Average[/TD]
[/TR]
[TR]
[TD="align: right"]155[/TD]
[TD]Poor[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

do you want it to return Excellent if below 0 too ? then use:

=LOOKUP(F3,{-9.99999999999E+307,80,90,110,150},{"Excellent","Good","Average","Below Average","Poor"})

 

[sachin483]

Thanks a lot

 

[sachin483]

when i adding the formula for 25 value it is showing "Below average" it should show "Average" as per formula

=LOOKUP(F9,{-9.99999999999E+307,10,20,30,31},{"Poor","Poor","Below Average","Average","Good"})


[TABLE="width: 165"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]-99[/TD]
[TD]Poor[/TD]
[/TR]
[TR]
[TD]<=10[/TD]
[TD]Poor[/TD]
[/TR]
[TR]
[TD]<=20[/TD]
[TD]below Average[/TD]
[/TR]
[TR]
[TD]<=30[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]>31[/TD]
[TD]Good[/TD]
[/TR]
</tbody>[/TABLE]