=VLOOKUP(A2,{100,6;200,7;300,8;400,9;500,#N/A},2,TRUE)

You can inbed a table like this into the VLOOKUP function, or use an actual
table. You will get an error if A2 is <100 ro 500. If you want to expand
the list (say to 1000+ - 15) you could do it like this.

=VLOOKUP(A2,{100,6;200,7;300,8;400,9;500,10;600,11 ;700,12;800,13;900,14;1000,15},2,TRUE)

with this formula anything <100 will result in an error, and anything 1000
is 15. You can use an IF statement to change this.

The VLOOKUP function finds the next smallest number and matches the value in
the table. So 243 will look at 200 and check column 2 of the table resulting
in 7.

Since you have a mathematical relationship, you can probably use a formula
like

=INT(A2/100)+5

This won't be exactly what you wanted (100-199 - 6, 200-299 - 7, etc.),
but you can use this formula to get closer

=INT((A2-1)/100)+5

for (101-200 - 6, 201-300 - 7, etc.)

"AC man" wrote:

Thank you, if I want to continue on with the IF stetments how should I write
it.
If the range in A1 is 100-200 I want it to say 6" in A2
If the range in A1 is 201-300 I want it to say 7" in A2
If the range in A1 is 301-400 I want it to say 8" in A2
If the range in A1 is 401-500 I want it to say 9" in A2
and so forth...
What would the code look like, you dont have to write it all the way out.
Just enough so I can get an idea. Thank you.


"CLR" wrote:

=IF(AND(A2100,A2<200),6,"")

Vaya con Dios,
Chuck, CABGx3



"AC man" wrote:

I am trying to creat an IF statement that will be true if the number falls
within a range of numbers. I want it to say IF A2 is less than 200 but
greater than 100 then this cell will display 6". I only know how to write an
IF statement for a less than or equal to statment but I dont know how to make
it fall in a specific range of numbers. please help thanks.