Group numbers with VLOOKUP in Excel

by

This tutorial shows how to Group numbers with VLOOKUP in Excel using the example below;

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.

Worked Example:   Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

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.

Worked Example:   Get first text value in a list in Excel

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.

Worked Example:   Map text to numbers in Excel

Pivot tables

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

Leave a Reply

Your email address will not be published. Required fields are marked *