Vlookup Date in Timestamp Column in Google Sheets

Published on

To Vlookup date in a timestamp column in Google Sheets, we must know two important things. What are they?

The first important thing is the function or formula that requires to convert the timestamp column to a date column.

This is because a timestamp contains both the date and time of the day. Remember! We want to Vlookup (vertical lookup) a date in a time-stamp column not a time-stamp in a time-stamp column.

Regarding the function(s) required for the same, there are two options in front of me. One is using the INT function and the other is using the toDate function.

toDate?

Yep! It’s a scalar function in Query which we can use part of our formula (as an expression) to Vlookup a date in a timestamp column in Google Sheets.

I’ve already written two tutorials on the usage of the above said two functions. If you have time, just go through the said two tutorials (not a must in the point of view of this tutorial). Here are them.

Here is the second important thing.

After converting the timestamp column, we must know how to use that column with the other columns in the table as the Vlookup range.

Didn’t get?

Example to Timestamp Problem in Vertical Lookup

Assume there are 3 columns – Timestamp, ID, and Amount – in a table (scroll down to see the table [TABLE # 1] below).

Our Vlookup search_key is a date. So we should convert the first column values from timestamps to dates and then combine the other two columns with it to use as the ‘range’ in Vlookup.

To know the ‘search_key’, and ‘range’ argument, take a look at the Vlookup syntax below.

Syntax:

VLOOKUP(search_key, range, index, [is_sorted])

As per the syntax, to Vlookup the search_key (date) in the ‘range’, the first column in the ‘range’ must be a date column.

As you may know, the Vlookup searches down the search_key in the first column (I’ll explain later what to do if the timestamp column is not the first column).

In our case, the first column is a timestamp column. So we must tame the Vlookup to only consider dates in that column, not DateTime (time-stamp). Here is how to do it.

How to Vlookup Date in a Timestamp Column in Google Sheets?

I have mentioned two functions above. They are INT and toDate.

That means I have two solutions to provide you with to Vlookup a date in a timestamp/DateTime column in Google Sheets.

First I am starting with the toDate. Before that here is a basic sample data with only three columns that in the range A1:C6.

TABLE # 1:

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

toDate with Vlookup

I want to use the date 27/08/2020 as the Vlookup search_key which is in cell E1 (scroll down to see the image).

First I’m going to modify the table virtually using Query so that we can use that expression as the ‘range’ in Vlookup.

=query(A1:C6,"Select toDate(A),B,C",1)

The above formula is the ‘range’ to use in Vlookup. The formula converts the first column in the table from timestamps to dates.

See the toDate use in the formula with column A. It keeps the other two columns (B and C) without any changes.

Here are the Vlookup formulas that use a date to search in the timestamp column.

To return value from the second column:

=VLOOKUP(E1,query(A1:C6,"Select toDate(A),B,C",1),2,0)

To return value from the third column:

=VLOOKUP(E1,query(A1:C6,"Select toDate(A),B,C",1),2,0)

Do you want values from the second and third columns with a single Vlookup? Then try this.

=ArrayFormula(VLOOKUP(E1,query(A1:C6,"Select toDate(A),B,C",1),{1,2},0))
Vlookup Date in a Timestamp Column in Google Sheets

Here in the above example, the additional ArrayFormula is necessary as we want to return two values from a non-array Vlookup.

This way, using Query as the ‘range’ (expression), we can solve the problem, i.e. Vlookup date in a timestamp column in Google Sheets.

Note: If you wish to hard code the search_key, then don’t enter it as “27/08/2020”. Instead, use the DATE function as Date(2020,8,27). Here is an example.

=ArrayFormula(VLOOKUP(date(2020,8,27),query(A1:C6,"Select toDate(A),B,C",1),{1,2},0))

INT with Vlookup

This is the most popular option among Google Sheets pro users to Vlookup a date in a timestamp column.

Let’s see how we can use the INT to format the timestamp column.

Here the virtual ‘range’ (expression) can be created as below (an equivalent to our earlier Query formula).

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

Using INT we have converted the timestamps to date, then retained the other two columns with the converted timestamp column as an array using Curly Brackets.

Now when using the above range with Vlookup, please do note that you must use the ArrayFormula whether the Vlookup returns 1 value or more than one value.

Example:

=ArrayFormula(vlookup(E1,{int(A2:A6),B2:C6},2,0))

It would return “KL604000” as its the ID in the second column in the range against the date in the first column, i.e. 27/08/2020.

Vlookup Date in Timestamp Column When the Timestamp Isn’t the First Column

Let me shuffle the columns in the first table here to move the timestamp column. Then I’ll explain how to use the shuffled table as the ‘range’.

Here is the shuffled table.

TABLE # 2:

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

Here the DateTime (timestamp) column is the second column. So here is how I am using Query toDate as well as the INT to create the ‘range’ (expression).

Query toDate ‘range’:

query(A1:C6,"Select toDate(B),A,C",1)
Vlookup range using toDate scalar function

INT formula ‘range’:

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

The Vlookup formulas will be the same as per the earlier examples corresponding to these functions.

That’s all about how to Vlookup a date in a timestamp column in Google Sheets.

Thanks for the stay. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.