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

Lookup and Reference Examples

  • Count rows that contain specific values in Excel
  • How to use Excel INDIRECT Function
  • Convert text string to valid reference in Excel using Indirect function
  • How to retrieve first match between two ranges in Excel
  • How to use Excel TRANSPOSE Function

Data Analysis Examples

  • How To Insert and Customize Sparklines in Excel
  • How to count table columns in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Get column name from index in Excel Table
  • Conflicting Multiple Conditional Formatting Rules in Excel

Data Validation Examples

  • Excel Data validation date in specific year
  • Data validation must not exist in list
  • Excel Data validation unique values only
  • Excel Data validation whole percentage only
  • Excel Data validation exists in list

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.

Worked Example:   How to calculate two-way lookup VLOOKUP in Excel Table

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.

Worked Example:   Get nth match with VLOOKUP in Excel

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).

Worked Example:   Lookup with variable sheet name in Excel

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:

Map text to numbers in Excel

Next Post:

Running count group by n size 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
  • Remove last characters from right in a cell in Excel
  • How to convert text string to array in Excel
  • Excel MAX, MATCH and ADDRESS function illustration
  • How to find and replace multiple values at same time in Excel
  • Get position of 2nd 3rd and more instance of character in Excel
  • How to calculate most recent day of week in Excel
  • How to enter Today’s Date or Static Date and Time in Excel
  • Get days, months, and years between dates in Excel
  • Convert decimal seconds to Excel time
  • Get days before a date in Excel
  • COUPDAYS function: Description, Usage, Syntax, Examples and Explanation
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
  • TBILLEQ function: Description, Usage, Syntax, Examples and Explanation
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate present value of annuity in excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles