How to Group Ages into Brackets in Excel

Assign each person an age bracket with an approximate lookup, count a bracket with COUNTIFS, and find the bracket of the median member.

You will learn to

• Look up a bracket label from a table of lower bounds
• Count values inside one bracket
• Combine MEDIAN with a bracket lookup

How it works

The cleanest way to bin ages (or prices, scores or order sizes) is a small table of lower bounds and labels: 0 → Under 18, 18 → 18–24, 25 → 25–34 and so on, sorted ascending. An approximate-match lookup returns the label of the largest lower bound that is less than or equal to the age: =LOOKUP(B2,$E$2:$E$8,$F$2:$F$8). VLOOKUP(B2,$E$2:$F$8,2,TRUE) and XLOOKUP with match_mode -1 do the same.

This beats a long nested IF: changing the brackets is a table edit, not a formula rewrite. To count members in a bracket, use COUNTIFS with ">=" the bracket’s lower bound and "<" the next bracket’s lower bound.

=LOOKUP(B2,$E$2:$E$8,$F$2:$F$8)