"IF" Formula to provide an answer when a Cell is Within a Range

[jagogo]

I'm trying to create an "IF" formula that will indicate a number when a cell falls within a given range. More specifically, let's say that if B2 is between 500-1000, then I want 1 in C2, if between 1001-2000, 2 and if between 2001-3000,3. How do I create the formula in C2?

Thanks for your help.

[dilipandey]

Hi jagogo,

Welcome to the forum.

use the below formula in c2:-

=IF(AND(B2>=500,B2<=1000),1,IF(AND(B2>=1001,B2<=2000),2,IF(AND(B2>=2001,B2>=3000),3,"")))

Regards,
DILIPandey

<click on below 'star' if this helps>

[Only4Excel]

=lookup(b2,{-1e+100,500,1001,2001,3001},{"",1,2,3,""})

[JBeaucaire]

Going strictly by your question, presuming the values will be 500+ each time:

=IF(B2="", "", MATCH(B2, {500,1001,2001}, 1))


Else, the option in post #3 is best.

[jagogo]

Thanks for chiming in! Unfortunately I've tried all three of these (making minor adjustments to meet my data more exactly) and am receiving an error message for each one (508, 508 and 512 respectively). Actually, as I'm writing this I just realized that I am currently using Open Office. In general I've found that it accepts the same formulas as Excel, but perhaps there is an issue there?

Very specifically I need to take the data from D2 (and subsequently the rest of the column once I have the formula correct) and use that information against this set of ranges:
<100 = 5
101-1000= 7
1001-5000 = 10
5001-10,000 = 13
10,001-50,000 = 15
50,001-100,000 = 14
100,001-500,000 = 12
500,001-1,000,000 = 9
1,000,001-5,000,000 = 7
5,000,001+ = 5

Other thoughts are greatly appreciated!

[JBeaucaire]

The Excel function for that would be:


=IF(B2="", "", LOOKUP(B2,{0,101,1001,5001,10001,50001,100001,500001,1000001,5000001},{5,7,10,13,15,14,12,9,7,5}))

[jagogo]

Thanks J. Unfortunately I'm still getting that ERR:812 message in OOO, but my husband has excel on his machine and I'll try it as soon as I can.

[jagogo]

Awesome Jerry! I just sent it over via IM to my husband and it works! Thanks so much for the quick and accurate response! I guess OOO has it's limitations after all.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.