Exceljet

Quick, clean, and to the point

Group numbers with VLOOKUP

Excel formula: Group numbers with VLOOKUP
Generic formula 
=VLOOKUP(value,group_table,column,TRUE)
Explanation 

If you need to group by number, you can use the VLOOKUP function with a custom grouping table. This allows you to make completely custom or arbitrary groups.

In the example shown, the formula in F7 is:

=VLOOKUP(D5,age_table,2,TRUE)

How this formula works

This formula uses the value in cell D5 for a lookup value, the named range "age_table" (G5:H8) for the lookup table, 2 to indicate "2nd column", and TRUE as the last argument indicate approximate match.

Note: the last argument is optional, and defaults to TRUE, but I like to explicitly set the matching mode.

VLOOKUP simply looks up the age and returns the group name from the 2nd column in the table. This column can contain any values that you wish.

Pivot tables

Pivot tables can automatically group numbers, but the VLOOKUP approach allows you to perform completely custom grouping.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.