This guide will explain how you can use VLOOKUP with a given time range in Excel.
We can set up a lookup table that will enable the user to search through a list of events with a specified time or duration.
Let’s take a look at a simple example where we could use the VLOOKUP function with a time range as a lookup value.
Suppose you have a list of events you want to attend. All events occur in a single day.
Each event has a specified event name, speaker, starting time and end time. You want to determine which events start after your lunch break.
You can use VLOOKUP to search through your list of events and return the event that starts at the specified time.
This use case is just one way we can use time ranges as a lookup value for the VLOOKUP function in Excel.
Now that we have a grasp on when to perform a lookup using time ranges, let’s look at an actual sample spreadsheet to see how it works in practice.
A Real Example of Using VLOOKUP with a Time Range in Excel.
The following section provides several examples of how to use the VLOOKUP function with a time range. We will also go into detail about the formulas and tools used in these examples.
First, let’s take a look at a real example of this type of lookup table being used in an Excel spreadsheet.
The table below contains a list of several talks and seminars. Each event has an indicated starting time, event name, and speaker. Given the starting time, we were able to use VLOOKUP to obtain the event name and speaker.
To get the event name, we just need to use the following formula:
=VLOOKUP(E17;B4:D15;2;FALSE)
We may also use the LOOKUP function if you need to use two time values to retrieve data from a lookup table.
To get the event name, we just need to use the following formula:
=LOOKUP(2;1/(B5:B15<=E17)/(C5:C15>=E18);D5:D15)
Let’s try to understand how this formula works. The LOOKUP function returns the approximate match in a range that is either a single row or a single column.
The argument 1/(B5:B15<=E17)/(C5:C15>=E18) will return a 1-dimensional vertical range. If the event starts and ends during the indicated time range, the corresponding value in the generated 1-dimensional range would be 1.
If no such match exists, the LOOKUP function will return a #N/A! error. However, if an event falls within the time range, a value of 1 would be enough to signal to the LOOKUP function that we have a match.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try using VLOOKUP and LOOKUP with time values, head over to the next section to read our step-by-step tutorial on how to do it yourself!
How to Perform a VLOOKUP with Time Range in Excel
This section will guide you through each step needed to perform a VLOOKUP given a time range in Excel. You’ll learn how we can retrieve values given one or two time values.
Follow these steps to start using the VLOOKUP function:
- Let’s start using
VLOOKUPwhen given a single time value. First, select the cell where you want to place the final result. In this example, we’ll use cell D19.
- Next, type the equal sign ‘=’ followed by the function name ‘VLOOKUP’.
- The first argument of the
VLOOKUPfunction should be the lookup value. In the example below, we’ve also selected cell D17 which contains the time value of ‘3:00:00 PM’.
- Next, select the lookup table as the second argument. In this example, we’ll use the cell range B4:D15. For the third argument, select the column in the lookup table you want to return.
- Hit the Enter key to evaluate the
VLOOKUPfunction. In our example, the function was able to retrieve the class that starts at 3 PM.
- The user may modify the same formula to retrieve other fields from the lookup table.
- To retrieve an item based on both a start and end time, we can also use our
LOOKUPfunction instead.
These are all the steps needed to perform a VLOOKUP with a time range in Excel.
This step-by-step guide should provide you with all the information you need to begin using a lookup table with time ranges. Overall, we’ve shown how you can use the VLOOKUP and LOOKUP functions to handle time values.
This function is just one example of the many Excel functions that you can use in your spreadsheets. Our website also offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
With so many other Excel functions available, you can find one that is appropriate for your use case. Subscribe to our newsletter, and you’ll get exclusive access to the latest spreadsheet guides and tutorials from us.
