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

In many cases, we may need to group the age range in Excel using VLOOKUP. So, a large dataset of ages will become of specific values. And, you can easily count them in an age range via a pivot table in Excel. Otherwise, you have to make complex formulas with the COUNT function. So, I will show you how to group the age range in Excel with the VLOOKUP function in this article.


Download Practice Workbook

You can download the practice workbook from here:


Steps to Group Age Range with Excel VLOOKUP Function

Suppose, you have a dataset of 9 persons of different ages and now you want to group ages in range with the VLOOKUP function in Excel. Alternatively, you can also group them using Pivot Table. Here, I am showing the steps to group age range in Excel with the VLOOKUP function in Excel.

📌 Step 1: Create a List of Age Range

  • First, make the dataset ready. And, make a table where the age ranges are listed.

How to Group Age Range in Excel Vlookup


📌 Step 2: Apply VLOOKUP Formula

  • Now, paste this formula into cell E5.
=VLOOKUP(D5,$G$5:$H$9,2,1)

How to Group Age Range in Excel Vlookup

  • Then, use the Fill handle icon to drag the formula to the other cells of the column or use Ctrl+C and Ctrl+P to copy and paste the formula to the other cells.

Apply VLOOKUP Formula

  • Now, you have got the Age Group column filled with the age groups for the cell in the Age column,
  • Thus, you have grouped the ages in range with the Excel VLOOKUP function.

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.


Conclusion

In this article, you have found the solution for the problem of how to group age range in Excel VLOOKUP. You can download the practice workbook for free. I believe, all the confusion regarding how to group age range in Excel VLOOKUP is solved after reading this article. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo