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

Data Analysis

  • Get column index in Excel Table
  • How to Create Column Chart in Excel
  • How To Compare Two Lists in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to create running total in an Excel Table

References

  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Basic INDEX MATCH approximate in Excel
  • Create hyperlink with VLOOKUP in Excel
  • How to use Excel VLOOKUP Function
  • INDEX function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Data validation must not exist in list
  • Excel Data validation allow weekday 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

  • OR function Examples in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • How to use Excel TRUE Function

Date Time

  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation
  • Convert Excel time to decimal seconds
  • Series of dates by day
  • Assign points based on late time in Excel
  • Pad week numbers with zeros in Excel

Grouping

  • Running count group by n size in Excel
  • How to randomly assign people to groups in Excel
  • If cell contains one of many things in Excel
  • Group arbitrary text values in Excel
  • Group times into unequal buckets in Excel

General

  • Index and match on multiple columns in Excel
  • How to set or clear a print area in Excel Worksheet
  • Subtotal by invoice number in Excel
  • Check if multiple cells have same value in Excel
  • How to create dynamic worksheet reference in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning