# Thread: checking whether a value is within a range

1. Registered User
Join Date
Mar 2010
Posts
30

## Unanswered: checking whether a value is within a range

Hello,
I have a feeling that my problem is really simple, but I can’t figure it out.
In column A I have a list of values ranging from 1-150
In column B I want Excel to insert a specific number, if the value in column A falls within a certain range.
The ranges are:
1-2
2-4
4-8
8-16
16-32
32-64
64-128

For example:
If the value in column A is 1.8, i.e., within the range of the first group, the number in column B should be 1
If the value in column A is 9, i.e., within the range of 4th group, then the number in column B should be 4
Etc...

Thanks a lot for your help!

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
There are two things to do.

1) Don't have the number ranges (such as "1-2") as text in a single cell. Instead, use two columns like this:
Code:
```A B
1 2
2 4

etc...```
2) At the moment there are overlaps in your number ranges. The number 2 falls into category 1 and category 2. Please clarify?

Hope that helps...

3. Registered User
Join Date
Mar 2010
Posts
30
Thanks Colin.
You are right, the correct ranges are:
1-1.9
2-3.9
4-7.9
8-15.9
16-31.9
32-63.9
64-128

Do i need to use IF within IF... until i cover all the different groups?

4. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

Thanks for clarifying. That makes things nice and easy.

Do i need to use IF within IF... until i cover all the different groups?
You could do that, but there's a better way.

In fact, in this case you only need 1 column which contains the minima for each number grouping. Suppose, for example, that you have this in cells A1:A7:
Code:
```1
2
4
8
16
32
64```
Now we can use the MATCH() function. For example, where does 1.2 fall into?
=MATCH(1.2,A1:A7,1)

Or, where does 9.3 fall into?
=MATCH(9.3,A1:A7,1)

General Notes
• With this set up, if you try to look up using a number less than 1 (which is the smallest number in the look up table) then the formula will return #N/A. If you want to avoid #N/A errors then the first number in your lookup table must always contain a value lower than anything you would ever lookup from.
• Any number equal to or greater than 64 (which is the largest number in the look up table) will return 7 (which is the last "group").
• The lookup table must be sorted smallest to largest (ascending).

MATCH() Function Notes

The 1 argument in the third ([match_type]) parameter has the following meaning per the Excel helpfile:
Originally Posted by Helpfile
Match_type Behavior
1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
Hope that helps...

PS. Welcome to the forum.

5. Registered User
Join Date
Mar 2010
Posts
30
It worked!!!
I've been trying to use IF function for hours with no luck...

Thanks a lot for your help, and i'm happy that i found this forum

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•