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.

• How to Extract Date From Timestamp in Google Sheets.
• How to Use the toDate Scalar Function in Google Sheets Query.

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:

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))

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:

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)

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!