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.
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 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.
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.
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 (i.e. group by 2 hours, group by 3 hours, etc.) you can do so with a rounding function called FLOOR. In the example shown, we have a number of transactions, each with a time stamp. Let's say you want to group...
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the...
In this video, we look at how to configure VLOOKUP to look up values based on an approximate match. This is good for things like tax rates, postage, grades, and commissions.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.