Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Learn How to Group Numbers with VLOOKUP

When using Excel, we often have a list of groups number, and it is hard to sort by group. Therefore, if your goal is to group by number in Excel, this tutorial will show you how to group numbers with the VLOOKUP function.



Group numbers in Excel with the VLOOKUP function

You can also categorize the group numbers according to a default function. 

Figure 1. Example of how to find group numbers with VLOOKUP in Excel

Formula for VLOOKUP

=VLOOKUP(value,group_table,column,TRUE)

Explanation of formula

If you need to categorize by number then, you can customize the VLOOKUP function for meeting the requirements. The customization enables a user to create arbitrary groups or intervals for grouping numbers with VLOOKUP. For instance, the above-attached screenshot discusses the age vs. age group of people belonging to several demographics. A user is asked to sort out the given ages into group forms.

Here, Column D illustrates ‘age’ which would be taken into factor along with ‘age’ and ‘group’ columns illustrated in Column G and Column H respectively. Next, a user would enter the equation into Column E, where the age group of a corresponding age would be specified—once the user presses ENTER. Let’s have a closer look at how the formula works:

How does the formula work?

Figure 1 demonstrates the formula bar with the VLOOKUP function. We have used the following formula for sorting group numbers with VLOOKUP custom function:

=VLOOKUP(D5,G5:H8,2,TRUE)

Figure 2. Example of how to find group numbers with VLOOKUP in Excel

The formula considers the value of D5 as its lookup value. Next, it focuses on G5:H8 as its age_table. Here, 2 indicate that the values are the location in the 2nd column index (Column H). Last but not least, we add TRUE to the equation which serves the purpose of approximate match mode in the equation.

Once we have entered an enclosed the variables and functions—the VLOOKUP function with the scan through the age group, and it would scan the corresponding group name from the 2nd index column. The column can contain customizable text or numeric values as per the preference of the user. Let’s have a look at the following example for understanding the uniqueness and versatility of the formula:

Figure 3. Example of how to find group numbers with VLOOKUP in Excel

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I have a pivot table in which per order (on the rows) stands how much products they ordered per size (on the columns). I want to determine which combinations of quantities of sizes people order. And I want to count these combinations.
Solved by E. W. in 60 mins
I have 500 numbers in column A with aproximatly 15digits. In column B I have 5 numbers with 6 digits Can a find a formula wich can transfer all numbers from A column wich start with some 6 digitsa from B column
Solved by V. L. in 32 mins
I filtered data in column A (it is labeled ID #) of a data set. On my next worksheet, column A is also the ID #, but it is a different data set. I want to filter the ID #'s the same for the two sheets, but how do I do that being that I am working with two different sets of data?
Solved by V. H. in 22 mins
I have a sheet with 3 columns. First column is a code for records in column B (has 563 records). What I need is a formula to show me if what is in column C (has 4400 records) is in column B and if it is to take the code for that record. Example: Column A(code corresponding to column B): 12, 14, 15, 19 Column B(names): asd, adf, ade, aqw Column C(names): akd, adf, ade, anb, wgs I need something that would say, if record in column C (for example adf) is matching record in column B (adf) return the code from column A (the code corresponding to that record in column B),if not return blank.
Solved by C. J. in 8 mins
If the data in A matches the data in C, then I need the data in E to be placed in B
Solved by X. W. in 40 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc