Excel formula: Group numbers at uneven intervals

In this article, we’re going to show you how to group a set of numbers that do not have the same size. For this Excel operation, once again, we’d be using the LOOKUP function.

Formula

= LOOKUP (value, intervals, groups)

In explaining this formula, we’ll take a look at an example.

Example

Imagine we’re tasked with the grouping of people based on their ages.

Figure 1. Excel showing the names and ages of people.

A look at the table above shows you a lot of things. First, we have the names of people and their ages, which are as different as their ages.

In the two other columns, we have another “age” column, which represents the intervals. And then, the Grade, which represents the groups. You can take another look at the formula to see this. You’d realize that our formula has the following arguments in it.

Intervals – refers to the named range from which the lookup values will be returned.
Groups – refers to the named range where certain values can be found in groups.

Next step

Use the formula to group numbers at uneven intervals here.

Our formula based on this example is: = LOOKUP (B3, E3:E6, F3:F6)

Where:

Age = E3:E6
Grade = F3:F6

Entering the formula in the first cell, C3, returns the group that the age in the adjoining cell belongs to.

Figure 2. LOOKUP function groups a number at an uneven interval.

Then, you can perform the same function for each cell to completely group all numbers at uneven intervals.

Notes

Based on our example, the LOOKUP does a match of the numbers in our column E and returns the corresponding value in the next column F.
Once the function finds a match in the Age column, it immediately supplies value from the associated group. Should the function not find a match, it will move across the age column until it finds a bigger value, before returning to the previous row.
Also, according to our example, where the highest age is 67, you can just have “60+” as the last group, instead of the “70” limit we had.
When arranging your intervals in a column, ensure that you do so in an alphabetical order. This way, you won’t have problems grouping numbers at uneven intervals.