How to Group Age Range in Excel with VLOOKUP (with Quick Steps)

Suppose you have a dataset of 9 persons of different ages and want to put them inside particular age groups.

Step 1 – Create a List of Age Ranges

  • Create a dataset and make a separate table where the age ranges are listed. Put the lower end of each group separately in a column, as noted on the image below.

How to Group Age Range in Excel Vlookup

Step 2 – Apply the VLOOKUP Formula

  • Apply the following formula into the result cell (E5 in the sample):

How to Group Age Range in Excel Vlookup

  • Use the Fill handle icon to drag the formula to the other cells of the column.

Apply VLOOKUP Formula

  • You have got the Age Group column filled with the age groups for the cell in the Age column.

Apply VLOOKUP Formula

Formula Explanation:

The formula, we have used to group ages in range with the Excel VLOOKUP function is:

  • =VLOOKUP(D5,$G$5:$H$9,2,1)

The Syntax of the VLOOKUP function is:

  • =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Lookup_value = D5:

It is a required value that it looks for in the leftmost column of the given table which can be a single value or an array of values.

So, it will look for the value of cell D5 in the leftmost column of the table. As a result, here, it will look for “28” in the 1st Column of the “Age Group with Range” table.

Table_array= $G$5:$H$9:

It is a required value which is the table where to look for the lookup_value in the leftmost column.

So, G5:H9 is the table of “Age Group with Range”. Here, you have to use absolute reference so you can copy and paste the formula into other cells keeping the table range fixed.

Col_index_num = 2:

It is also a required value which is the number of the column in the table from which a value is to be returned.

So, here the value 2 will result like it will return the value from the second column of the selected table.

[range_lookup] = 1:

It is an optional value that tells whether an exact or partial match of the lookup_value is required. Here put 0 for an exact match and 1 for a partial match. In addition, the Default is 1 (partial match).

So, here the value 1 will result in a partial match.

In the partial match, VLOOKUP takes the nearest smaller value from the list. So, for cell D5 where the value is 28 and the closest smaller value of 28 in the table is 16. As a result, it has taken the range 16-30.

Read More: How to Calculate Age in Excel from ID Number

Download the Practice Workbook

Related Articles

<< Go Back to Calculate Age | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF