Values in Multipule Ranges

[lawdog]

I am having problems writing a formula that will return a different number based on were a number falls in different ranges. Below is an example<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
499 500 400<o></o>
399 300<o></o>
299 200<o></o>
199 100<o></o>
<o> </o>
If the number in the first column fall in range one then 3, if range second range then 2, if the third range then 1. Any ideas?<o></o>

 

[jbeaucaire]

I am having problems writing a formula that will return a different number based on were a number falls in different ranges. If the number in the first column fall in range one then 3, if range second range then 2, if the third range then 1. Any ideas?

Your question seems to imply you're looking for a value between ranges of numbers, but then your sample data did not layout in clearly separated "ranges". Your data implied "exact match" type of data. So which is it? I reoriented the data to a more standard "range" format

Let's see if I was able to decipher your chart properly:

 A       B      C
500             1
400     499     2
300     399     3
200     299     4
100     199     5

You can lookup any value in that table and get the value from column C returned with a simple formula. For instance, if you had a value in cell E1 of 325, this formula would return a value of "3":

=LOOKUP(E1,$A$1:$A$5,$C$1:$C$5)

 

[lawdog]

A B C
345 500-400
399-300
299-200
What I am trying to do is write a formual that returns a 1 2 or 3 based on which range (the numbers in B and C) the number in A falls in.

 

[jbeaucaire]

A B C
345 500-400
399-300
299-200
What I am trying to do is write a formual that returns a 1 2 or 3 based on which range (the numbers in B and C) the number in A falls in.

Those are not standard formats. Used like so, the formula below will work:

Excel Workbook
	A	B	C	D	E
1	345	200	3		2
2		300	2
3		400	1
Sheet1

The yellow numbers represent the START of each range. When you start the next range, it provides the "top" of the first range automatically, so you don't need to show 200-299, just show 200 as the first start, then 300 as the next.

The blue numbers are the numbers you want to "match" for each range starting with the number in yellow.

The Green cell has the formula you wanted.