VLOOKUP by Date in a Timestamp Column in Google Sheets

Published on

To perform a VLOOKUP by date in a timestamp column in Google Sheets, we need to understand two key concepts. What are they?

1. Converting a Timestamp to a Date

A timestamp includes both the date and time, but we want to perform a VLOOKUP using only the date. This means we must convert the timestamp column to a date column before looking up values.

There are two functions that can help with this:

  • The INT function
  • The toDate function (used within QUERY)

What is toDate?

It’s a scalar function in QUERY that we can use to extract only the date portion of a timestamp. This function ensures that VLOOKUP by date in a timestamp column works correctly.

2. Using the Modified Column in VLOOKUP

Once we’ve converted the timestamp column, we must ensure that it is correctly used in the VLOOKUP range along with the other relevant columns.

Didn’t get it? Let’s go through an example.

Example: VLOOKUP Issue with Timestamps

Assume we have a table with three columns: Timestamp, ID, and Amount (see Table #1 below).

DateTimeIDAmount
25/08/2020 12:10:15KL601001500
26/08/2020 13:00:10KL591050500
27/08/2020 12:35:01KL604000450
28/08/2020 11:49:00KL6040020
29/08/2020 12:10:01KL592015450

Our VLOOKUP search key is a date (e.g., 27/08/2020). Since column A contains timestamps, we must extract only the date before using it in VLOOKUP.

Method 1: Using QUERY with toDate

We modify the table virtually using QUERY, converting column A (timestamps) to dates.

Step 1: Creating the VLOOKUP Range

=QUERY(A1:C6, "SELECT toDate(A), B, C", 1)

This formula:

  • Extracts only the date from column A
  • Keeps columns B and C unchanged
  • Forms the correct VLOOKUP range

Step 2: Using VLOOKUP with the Modified Range

To return values from different columns based on the search key 27/08/2020 entered in cell E1:

  • Fetch ID:=VLOOKUP(E1, QUERY(A1:C6, "SELECT toDate(A), B, C", 1), 2, 0)
  • Fetch Amount:=VLOOKUP(E1, QUERY(A1:C6, "SELECT toDate(A), B, C", 1), 3, 0)
  • Fetch both ID and Amount in one formula:=ArrayFormula(VLOOKUP(E1, QUERY(A1:C6, "SELECT toDate(A), B, C", 1), {2, 3}, 0))
Example of VLOOKUP by date in a timestamp column in Google Sheets

Note: If you are hardcoding the search key, use the DATE function instead of entering text:

=ArrayFormula(VLOOKUP(DATE(2020, 8, 27), QUERY(A1:C6, "SELECT toDate(A), B, C", 1), {2, 3}, 0))

Method 2: Using INT Function

This is another efficient method for VLOOKUP by date in a timestamp column. The INT function removes the time portion, leaving only the date.

Step 1: Creating the VLOOKUP Range

={INT(A2:A6), B2:C6}

This formula:

  • Converts timestamps to dates using INT
  • Retains columns B and C

Step 2: Using VLOOKUP with INT

=ArrayFormula(VLOOKUP(E1, {INT(A2:A6), B2:C6}, 2, 0))

This returns KL604000, the ID corresponding to 27/08/2020.

VLOOKUP by Date in a Timestamp Column When the Timestamp Isn’t the First Column

What if the timestamp column isn’t the first column? Consider this shuffled table:

IDDateTimeAmount
KL60100125/08/2020 12:10:15500
KL59105026/08/2020 13:00:10500
KL60400027/08/2020 12:35:01450
KL60400228/08/2020 11:49:000
KL59201529/08/2020 12:10:01450

Since the timestamp column is now the second column, we must adjust our QUERY and INT formulas.

Using QUERY

=QUERY(A1:C6, "SELECT toDate(B), A, C", 1)
Manipulating data using the toDate QUERY function for VLOOKUP in Google Sheets

Using INT

={INT(B2:B6), A2:A6, C2:C6}

The VLOOKUP formulas remain the same as before, but now reference column B instead of A.

Final Thoughts

Now you know how to perform VLOOKUP by date in a timestamp column in Google Sheets using two powerful methods: QUERY with toDate and the INT function.

Both methods ensure that VLOOKUP searches only dates, not full timestamps, allowing for accurate results.

Thanks for reading!

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.