IF match number THEN return other number

**godlev** · 10-12-2011, 04:04 AM

Hi all,

can someone please help me with this:

IF cell A1 = from 0 to 2 then cell B1 = 0
IF cell A1 = from 3 to 4 then cell B1 = 2
IF cell A1 = from 5 to 6 then cell B1 = 4
IF cell A1 = 7 or higher then cell B1 = 6

i guess its easy formula but im newbie in excel and my formula syntax sucks.

Thank you in advance!

**inayat** · 10-12-2011, 04:11 AM

Try this:

=IF(A1<2.01,0,IF(A1<4.01,2,IF(A1<6.01,4,6)))

**MUSTAFAINE** · 10-12-2011, 04:12 AM

to Worksheet_SelectionChange :

Select Case [A1].Value
Case Is > 0
[b1] = 0
Case Is > 2
[b1] = 2
Case Is > 4
[b1] = 4
Case Is > 6
[b1] = 6
End Select

**JBeaucaire** · 10-12-2011, 04:14 AM

Try this in B1:

=LOOKUP(A1, {0,3,5,7}, {0,2,4,6})

**Bob Phillips** · 10-12-2011, 04:15 AM

Try

=INDEX({0,2,4,6},MATCH(A1,{0,3,5,7}))

**zbor** · 10-12-2011, 04:15 AM

maybe this:

=MIN(6, 2*INT(A1/2))

**godlev** · 10-12-2011, 04:15 AM

Thank you Inayat your example matches my requirements perfectly - Thanks!
This thread is solved!

**Bob Phillips** · 10-12-2011, 04:19 AM

Originally Posted by godlev

Thank you Inayat your example matches my requirements perfectly - Thanks!
This thread is solved!

Wrong choice!

**zbor** · 10-12-2011, 04:22 AM

I agree with Bob Phillips,
but even if you using this solution you should notice that it will return wrong result for 2,005

so, NEVER use this sintax (for example for less or equal 12 DON'T use <12,0001)

You have <= sign, so your formula became:

=IF(A1<=2,0,IF(A1<=4,2,IF(A1<=6,4,6)))