Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Excel Frequency Function Example
  • Get column index in Excel Table
  • Excel Pie Chart
  • How to Create One and Two Variable Data Tables in Excel
  • Example of COUNTIFS with variable table column in Excel

References

  • How to get address of first cell in range in Excel
  • How to get first column number in range in Excel
  • Count unique text values with criteria
  • Basic INDEX MATCH approximate in Excel
  • How to use Excel VLOOKUP Function

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation no punctuation
  • Excel Data validation date in next 30 days
  • Excel Data validation unique values only
  • Excel Data validation number multiple 100

Group times into unequal buckets in Excel

by

This tutorial shows how to Group times into unequal buckets in Excel using the example below;

Formula

=VLOOKUP(time,bucket_table,column,TRUE)

Explanation

This formula is a great example of how you can use VLOOKUP to group data in completely custom ways.

If you need to group times into buckets, but the buckets are not equal (i.e. 12 AM-7 AM, 7 AM-12 PM, etc.) you can do so with the VLOOKUP function set to approximate match.

The problem

There are several ways to group times in Excel. If you just need to group times by the hour, a pivot table is very quick and easy. If you need to group times into other equal buckets of multiple hours (i.e. 3 hours, 4 hours, etc.) a nice solution is to use the FLOOR function. However, if you need to group times into unequal buckets, you need to take a more custom approach. VLOOKUP, in its approximate match mode, allows you to group times into custom intervals of any size.

The solution

The solution is to build a lookup table that “maps” each time into the right bucket. In the first column, enter the start time for the bucket. In column two, enter the name of the bucket you want to use. The table must be sorted by the start time, smallest to largest.

Finally, configure VLOOKUP to look up each time in the bucket table with approximate match.

In the example shown, the formula n E5 is:

=VLOOKUP(D5,buckets,2,1)

D5 is the lookup value, “buckets” is a named range for G5:H8, 2 is the column index, and 1 is a flag that enables approximate match. (You can also use TRUE).

When VLOOKUP is in approximate match mode, it matches the nearest value that is less than or equal to the lookup value. In this way, you can think of the incoming lookup time as being “rounded down” into the right bucket.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

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

Logical Functions

  • Return blank if in Excel
  • OR function Examples in Excel
  • How to return blank in place of #DIV/0! error in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert time to time zone in Excel
  • Get last weekday in month in Excel
  • Display the current date in Excel
  • Sum through n months in Excel
  • Calculate number of hours between two times in Excel

Grouping

  • Categorize text with keywords in Excel
  • Map text to numbers in Excel
  • How to randomly assign people to groups in Excel
  • Running count group by n size in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Creating and Opening an existing file in Excel
  • How to calculate profit margin percentage in Excel
  • AutoFit Column Width, AutoFit Row Height in Excel
  • How to calculate decrease by percentage in Excel
  • List worksheet index numbers in Excel
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning