Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Left Lookup in Excel
  • How to get last row in mixed data with blanks in Excel
  • How to use Excel MMULT Function
  • Get nth match with INDEX / MATCH in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Analysis Examples

  • Filter Data Based on Date in Excel
  • How to calculate current stock or inventory in Excel
  • Get column name from index in Excel Table
  • Conditional Formatting Rules in Excel
  • How to Use Solver Tool in Excel

Data Validation Examples

  • Excel Data validation unique values only
  • Data validation must not exist in list
  • Excel Data validation don’t exceed total
  • Excel Data validation exists in list
  • Excel Data validation only dates between

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:   Map inputs to arbitrary values in Excel

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:   VLOOKUP with multiple criteria 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:   Merge tables with VLOOKUP in Excel

Pivot tables

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

Post navigation

Previous Post:

Group arbitrary text values in Excel

Next Post:

Group numbers at uneven intervals in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • LOWER function: Description, Usage, Syntax, Examples and Explanation
  • ASC function: Description, Usage, Syntax, Examples and Explanation
  • Remove leading and trailing spaces from text in one or more cells in Excel
  • Find function vs Search function in Excel
  • Split numbers from units of measure in Excel
  • List holidays between two dates in Excel
  • How to get same date next month or previous month in Excel
  • How to calculate nth day of year in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Add business days to date in Excel
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • Calculate periods for annuity in Excel
  • COUPNUM function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
  • DB function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles