Help Returning Multiple Values

**slavezero** · 06-24-2009, 05:08 PM

i'm having trouble creating a formula for this:

if (cell) is less than (75), return (60)
if cell is greater than 75, but less than 101, return 80
if cell is greater than 101, return 100

i've tried the IF/AND statements, however, it will only return 2 results (one for false and one for true) -- i need the third result.

which fuction should i use or how can i modify the IF statement to make this work?

**contaminated** · 06-24-2009, 05:14 PM

Maybe this

=IF(A1<75,60,IF(AND(A1>=75,A1<101),80,IF(A1>=101,100)))

**shg** · 06-24-2009, 05:33 PM

=LOOKUP(a1, {-9e+307,75,101},{60,80,100})

**slavezero** · 06-24-2009, 06:45 PM

the "LOOKUP" function worked great, i'd like to know how it works. i did have to change 75 to 76 though, so it would return 60 if the cell value was 75 - thanks

- as for the other "IF" formula, i believe i tried that before and it wouldn't work

**shg** · 06-24-2009, 06:52 PM

Change 308 to 307? There's no 308 in the formula.

if (cell) is less than (75), return (60)
if cell is greater than 75, but less than 101, return 80
if cell is greater than 101, return 100

By that description the formula doesn't return anything for 75 (or 101). I assumed

if cell < 75, return 60
if cell >= 75 and < 101, return 80
if cell >=101, return 100