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

Lookup and Reference Examples

  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Two-column Lookup in Excel
  • How to get relative row numbers in a range in Excel
  • Left Lookup in Excel
  • How to get first column number in range in Excel

Data Analysis Examples

  • Working With Tables in Excel
  • Filter Data Based on Date in Excel
  • Get column name from index in Excel Table
  • Conditional Formatting Data bars Examples in Excel
  • Excel Frequency Function Example

Data Validation Examples

  • Excel Data validation must begin with
  • Excel Data validation no punctuation
  • Excel Data validation allow weekday only
  • Excel Data validation date in specific year
  • Excel Data validation don’t exceed total

Group numbers at uneven intervals in Excel

by

This tutorial shows how to Group numbers at uneven intervals in Excel using the example below;

Formula

=LOOKUP(value,intervals,groups)

Explanation

To group numbers into intervals of unequal size, you can use the LOOKUP function. In the example shown, the LOOKUP function is used to group people by age into at intervals of unequal size. The formula in D5 is:

=LOOKUP(C5,age,group)

Where “age” is the named range F5:F8 and “group” is the named range G5:G8.

Worked Example:   Two-column Lookup in Excel

How this formula works

To do this, LOOKUP is configured as follows:

  • Lookup values are ages in column C
  • The lookup vector is the named range “age” (F5:F8)
  • The result vector is the named range “group” (G5:G8)

With this setup, LOOKUP performs an approximate match on the numeric values in column F, and returns the associated value from column G.

Worked Example:   Lookup last file version in Excel

The LOOKUP function always performs an approximate match, with the following behavior:

  • If LOOKUP finds an exact match in the age column, the corresponding group is returned.
  • If no exact match is found, LOOKUP will traverse the age column until a larger value is found, then “step back” to the previous row.
  • If an age is greater than 50 (the highest value), LOOKUP will return the group associated with 50 (“50+”).
  • If age is less than the smallest value in the age column, LOOKUP will return #N/A.
Worked Example:   Get last match cell contains in Excel

Note: ages must appear in ascending order. Double-check custom intervals – it’s easy to make a mistake 🙂

With hardcoded values

If you want to do this kind of grouping without a table on the worksheet, you can hardcode values into LOOKUP as array constants like this:

=LOOKUP(C5,{0,10,36,50},{"<10","10-35","36-49","50+"})

Post navigation

Previous Post:

Group numbers with VLOOKUP in Excel

Next Post:

Group times into 3 hour buckets 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
  • Get last name from name with comma — Manipulating NAMES in Excel
  • UPPER function: Description, Usage, Syntax, Examples and Explanation
  • Split dimensions into two parts in Excel Worksheet
  • How to find and replace multiple values at same time in Excel
  • Remove file extension from filename in Excel
  • Get last weekday in month in Excel
  • How to calculate project start date based on end date in Excel
  • Calculate days remaining in Excel
  • Generate series of dates by weekends in Excel
  • Assign points based on late time in Excel
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles