Unanswered: checking whether a value is within a range
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:
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
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:
Now we can use the MATCH() function. For example, where does 1.2 fall into?
Or, where does 9.3 fall into?
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
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.