Grouping in unequal bands

One of the most common queries is how to achieve grouping in unequal bands.

Rather than creating a massive IF function, use a VLOOKUP with TRUE as the range (using the approximate match). If you haven’t used VLOOKUP, watch the VLOOKUP video tutorial.

NEW: Go to  VLOOKUP Explanations with downloadable exercises and detailed solution

In the example below column A has a number of sale amounts and we want to group them in bands, but as shown in column D and E they are uneven (1st band is 500 then 4500 etc).

Create the table as shown in D2 to E5. In column D have the trigger numbers and in column E the description you want (this could be a number or text).

Now in Column B you can create a VLOOKUP that uses the sales amount and compares it to the table, pulling back the appropriate description. The key difference is that the range indicator must be TRUE.

Now that you have this column, you can use it in a filter or pivot table.

Grouping in unequal bands

Remember that if you use the TRUE option in VLOOKUP the table has to be in ascending order.