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.
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.
📌 Step 2: Apply VLOOKUP Formula
- Now, paste this formula into cell E5.
=VLOOKUP(D5,$G$5:$H$9,2,1)
- 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.
- 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.
🔎 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 Practice Workbook
You can download the practice workbook from here:
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. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Calculate Retirement Age in Excel
- Metabolic Age Calculator in Excel
- How to Calculate Average Age in Excel
<< Go Back to Calculate Age | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
