formula that can test multiple conditions

**harryhu** · 04-06-2009, 09:39 PM

Hi all

I am trying to use if formula to satisfy 5 conditions:

if a cell is 96% and over then return 5 to the next cell, ie, if E7 is 98% then F7 should be 5 so on so forth.
The rest of the scale looks like this:
88% 4
82% 3
76% 2
75% and below 1

Any clue on how to satisfy these 5 conditions in 1 formula?

Much appreciated!

Harry

**Paul** · 04-06-2009, 09:50 PM

Hello Harry,

Try:

=LOOKUP(E7,{0,0.76,0.82,0.88,0.96},{1,2,3,4,5})

**DonkeyOte** · 04-07-2009, 04:12 AM

Given return is 1 to 5

=MATCH(E7,{0,0.76,0.82,0.88,0.96})

LOOKUP is far more flexible though should you want to alter the result values...

**mubashir aziz** · 04-07-2009, 05:54 AM

Pj : I'm newly joined member here but i must say some formula's really amazing ...especially most of the time i used Vlookup to import data from other sheet but now i'm interested to learn about lookup

DonkeyOte : Your excel knowledge is simply terrific .... Your formulas always open new horizon .... keep it up .....