To retrieve data corresponding to the same time across different dates in Microsoft Excel 2019, you must combine date and time into a single datetime value because Excel stores them as serial numbers. By creating a lookup column that merges your target date with the specific time of interest, you can use standard functions like XLOOKUP or INDEX-MATCH to find the exact record. This approach ensures precision by avoiding errors caused by comparing separate date and time columns independently.
Understanding Excel’s Date-Time Storage
Before attempting any lookup, it is crucial to understand how Excel handles temporal data. Excel stores dates and times as serial numbers where the integer part represents the date (days since January 1, 1900) and the decimal fraction represents the time (portion of a 24-hour day). For example, the number 45000.5 represents noon on a specific day. If your source data has dates in one column and times in another, they are stored separately. To look up "10:00 AM on January 1st," you cannot simply search for "January 1st" and "10:00 AM" separately if you need an exact match for that specific combination. You need a composite key.
If you are struggling with formatting issues where Excel misinterprets your inputs, refer to
How to Tell Excel That a Number Is a Date in a Custom Date Format. Proper formatting is often the first step in ensuring your lookup values are recognized correctly as datetime objects rather than text strings.
Preparing Your Data Structure
The most robust method involves creating a helper column in your source data table. Assume your source data has Column A for Dates and Column B for Times, and Column C contains the values you want to retrieve.
First, insert a new column, let’s call it Column D, titled "DateTime". In the first cell of this column (D2), enter a formula that combines the date and time. The simplest way is using the CONCATENATE function or the ampersand operator, but arithmetic addition is more reliable for numerical consistency. Enter the following formula in D2:
=A2+B2
This formula adds the date serial number to the time serial number, resulting in a true datetime value. Drag this formula down to cover all rows in your dataset. Ensure that the cells in Column D are formatted with a custom format such as "yyyy-mm-dd hh:mm" so you can verify the combination is correct visually. If your times are missing (e.g., only dates are present), the time portion will default to midnight (0.0), which might not be what you intend. In such cases, ensure your time data is complete.
For more complex scenarios involving multiple inputs, see
How to Repeat a Function with Different Size Inputs in Microsoft Excel 2019, which may help if you are applying similar logic across large, varied datasets.
Creating the Lookup Value
Now that your source data has a unified datetime column, you need to create the lookup value in your result sheet. Suppose you want to find the data for December 25, 2023, at 14:30 (2:30 PM).
In your lookup sheet, create two input cells: one for the target date and one for the target time. Let’s say Cell F2 holds the date "2023-12-25" and Cell G2 holds the time "14:30". Similar to the source data preparation, you must combine these into a single datetime value for the lookup. In Cell H2, enter:
=F2+G2
Format Cell H2 with the same datetime format used in the source data. This creates the exact key that exists in your source table's Column D. If you do not combine them, Excel will fail to find a match because it will be looking for a date in a datetime column without the time component, or vice versa.
Executing the Lookup Formula
With the combined datetime value ready, you can now perform the lookup. In Excel 2019, the recommended function is XLOOKUP due to its flexibility and ease of use. However, INDEX-MATCH is also a powerful alternative if you prefer legacy compatibility or have specific array requirements.
Using XLOOKUP, place the following formula in the cell where you want the result:
=XLOOKUP(H2, SourceSheet!$D$2:$D$100, SourceSheet!$C$2:$C$100)
Here is the breakdown of the arguments:
-
H2: The lookup value (the combined date-time from your lookup sheet).
-
SourceSheet!$D$2:$D$100: The array containing the combined datetimes from your source data. Adjust the range to fit your actual data size.
-
SourceSheet!$C$2:$C$100: The return array containing the data you wish to retrieve.
If XLOOKUP is not available in your specific version or setup, use INDEX-MATCH with a compound criteria approach. Since we already created a helper column, the MATCH becomes straightforward:
=INDEX(SourceSheet!$C$2:$C$100, MATCH(H2, SourceSheet!$D$2:$D$100, 0))
This formula searches for the exact match of the combined datetime in the helper column and returns the corresponding value from the data column. It is critical that the ranges are identical in length and that there are no blank rows within the lookup array, as this can cause offset errors.
Troubleshooting Common Issues
Even with correct formulas, users often encounter #N/A errors. The most common cause is a mismatch in data types. Verify that both the lookup value and the lookup array are treated as numbers, not text. If your dates were imported from another system, they might be stored as text. Use the VALUE function to convert them:
=VALUE(A2)+B2.
Another issue arises from floating-point precision. Occasionally, the sum of a date and time might differ by a tiny fraction (e.g., 0.0000001) due to how decimals are handled. To mitigate this, round the combined datetime values in both the source and lookup sheets using the ROUND function:
=ROUND(A2+B2, 10). This ensures exact equality during the comparison.
Finally, ensure that your time values are consistent. If some entries use a 12-hour clock and others use a 24-hour clock, Excel will interpret them differently unless standardized. Check your regional settings and cell formats to guarantee uniformity. For guidance on managing date-time inputs efficiently, consider reviewing
Adding Date-Time to Excel Document Quickly: A Step-by-Step Guide.
Frequently Asked Questions
What if my time column is empty for some dates?
If the time column is empty, Excel treats it as zero, which corresponds to midnight. To handle this, you must decide whether to default to midnight or ignore those rows. You can add a condition in your formula to check if the time cell is blank before adding it, or filter out rows with missing times before creating the helper column.
Can I use VLOOKUP for this task?
VLOOKUP is not ideal for this specific scenario because it only looks up values in the first column of a range. Since your combined datetime helper column is likely in the middle or end of your data, VLOOKUP would require rearranging your columns or using a complex index-match workaround. XLOOKUP or INDEX-MATCH is far superior for flexible column referencing.
How do I handle time zones in my lookup?
Excel does not natively store time zone information. All times are treated as local to the workbook. If your data spans multiple time zones, you must manually adjust the times to a single reference zone (like UTC) before combining them with dates. Failure to do so will result in incorrect matches when comparing records from different regions.
Why am I getting a #VALUE! error?
A #VALUE! error typically indicates that one of the components being added (date or time) is not a valid serial number. Check for leading spaces in your date or time cells, which Excel may interpret as text. Use the TRIM function to clean the data, or re-enter the values to ensure they are recognized as numeric dates and times.
Is there a way to do this without helper columns?
Yes, you can use an array formula with SUMPRODUCT or AGGREGATE, but it is significantly slower on large datasets. For example, you could use
=INDEX(DataRange, MATCH(1, (DateCol=TargetDate)*(TimeCol=TargetTime), 0)). However, helper columns are generally preferred for performance and readability in Excel 2019.