The **VLOOKUP **is one of the most useful options in Google Sheets and Excel. This option is useful for searching and extracting information from a large dataset. Several functions like the **VLOOKUP**,** XLOOKUP**, or a combination of **INDEX** and **MATCH** functions, etc. can perform the **VLOOKUP** operation. While making datasheets in Google Sheets or Excel, we often use date values. So, a ques arrives, “Can we apply **VLOOKUP** by Date?”. The answer is “Yes”. Here, in this article, I’ll demonstrate 2 examples to apply **VLOOKUP** by date in Google Sheets.

## A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following link. The spreadsheets contain an overview of the datasheet and an outline of the described examples to apply **VLOOKUP** by date.

## 2 Helpful Examples to Apply VLOOKUP by Date in Google Sheets

First, let’s get familiar with our dataset. The dataset contains starting and ending dates of several projects and the estimated budget for these projects. We’ll extract the estimated budget data from the dataset based on date values. Note that the dataset here is sorted.

### Example 1: Using VLOOKUP by Date

Here, we’ll do a simple **VLOOKUP** based on a date value. We have to modify our datasheet like the following and enter any starting date from **Column B** in **Cell C10**.

__Steps:__

- First, select
**Cell C11**. - Then, type in the following formula:

`=VLOOKUP(C10,B5:D8,3,0)`

- Finally, press
**Enter**key to get the required result.

### Example 2: Applying VLOOKUP Between Two Dates

In the previous example, we used the exact date for looking up. But can we use any random date between two specific values? Yes, we can. Go through the following three examples to know how.

Also, instead of a starting date, we require a random date now. Therefore, modify the datasheet as follows:

#### Example 2.1: Implementing for Sorted Date Range

The **VLOOKUP** function will select the nearest match when it can’t find an exact match for the search key in a sorted data range.

__Steps:__

- First, select
**Cell C11**. - Afterward, type in the following formula:

`=VLOOKUP(C10,B5:D8,3,1)`

- Finally, press
**Enter**key to get the required result. As you can see, the Date**12/16/2022**doesn’t exactly match with any of the values in range**B5:C8**. But it falls between the dates**12/13/2022**and**12/31/2022**. Hence, the**VLOOKUP**function has extracted data from**Row 8**.

#### Example 2.2: Employing for Unsorted Date Range

The date range was sorted in the previous example. But what happens when the dataset is not sorted? Will the previous method work if the data range is not sorted? The answer is No, it won’t. For example, consider the following modification in the dataset. The date range is now unsorted.

Now, if we apply a formula like the previous method, we see an **#N/A** error. However, you can still perform a **VLOOKUP** using a combination of the **ARRAYFORMULA** and **LOOKUP** functions.

__Steps:__

- First, select
**Cell C11**. - After that, type in the following formula:

`=ARRAYFORMULA(LOOKUP(12,1/($B$5:$B<=C10)/($C$5:$C>=C10),$D$5:$D))`

- Finally, press
**Enter**key to get the required value.

__Formula Breakdown__

**LOOKUP(12,1/($B$5:$B<=C10)/($C$5:$C>=C10),$D$5:$D)**

First, the **LOOKUP** function searches for a random value of 12 provided as the search key in the search range. The search range here will be **1** or **#DIV/0! **error. The **LOOKUP** function will select the value for which it will find the last match. Finally, it returns the value from the second row of the result range **D5:D**.

**ARRAYFORMULA(LOOKUP(12,1/($B$5:$B<=C10)/($C$5:$C>=C10),$D$5:$D))**

The **ARRAYFORMULA** function helps to deal with the arrays in **LOOKUP** function arguments.

#### Example 2.3: Combining VLOOKUP and SORT Functions

If you find the previous method a little complex, you can simply combine the **SORT** function with the **VLOOKUP** function to virtually sort the date range.

__Steps:__

- First, select
**Cell C11**. - Afterward, type in the following formula:

`=VLOOKUP(C10,SORT(B5:D8),3,1)`

- Finally, press
**Enter**key to get the required result.

__Formula Breakdown__

**SORT(B5:D8)**

First, the **SORT **function sorts the rows of the range** B5:D8**.

**VLOOKUP(C10,SORT(B5:D8),3,1)**

Afterward, the **VLOOKUP** function will return the content of the third column in the sorted range where the closest match is found for the search key in **Cell C10**.

## What to Do When VLOOKUP Is Not Working with Dates in Google Sheets

There can be various reasons behind the **VLOOKUP** function not working with date ranges. We have already acknowledged one of the reasons in **Example 2.2** where the dataset was not sorted. **Example 2.2**** **and** Example 2.3** provided solutions for that problem. Here, we will discuss a different problem.

As we have seen in **Example 2.1**, the **VLOOKUP** function selects the nearest value if an exact match is not found in a sorted dataset. But in the following dataset, an **#N/A** error is returned for that similar formula.

The reason for the error is that the date range is formatted as “**Plain Text**” instead of “**Date**”. There can be reasons behind formatting the dates as plain text values. So, can we do **VLOOKUP** without changing the format? The answer is a resounding Yes! We can join the** INDEX**, **MATCH**, and **DATEVALUE** functions to get the required result. However, if the plain text format isn’t important, then simply changing the data format will work for applying the **VLOOKUP** function. We will discuss both of these solutions below.

#### Solution 1: Using DATEVALUE Function

- First, select
**Cell C11**. - Then, type in the following formula-

`=INDEX(D5:D8,MATCH(C10,DATEVALUE(B5:B8),1))`

- Finally, press
**Enter**key to get the required result.

__Formula Breakdown__

**DATEVALUE(B5:B8)**

First, the **DATEVALUE** function converts the date strings in the range **B5:B8** into date values.

**MATCH(C10,DATEVALUE(B5:B8),1)**

Afterward, the** MATCH** function returns the relative position of the cell in range **B5:B8** where a match is found for the search key provided in **Cell C10**.

**INDEX(D5:D8,MATCH(C10,DATEVALUE(B5:B8),1))**

Finally, the **INDEX** function returns the content of the subsequent cell in range **D5:D8**, where it finds a match for range **B5:B8**.

#### Solution 2: Applying Date Format

- First, select the range
**B5:C8**and change the data format to “**Date**”.

- Afterward, type in the following formula in
**Cell C11**:

`=VLOOKUP(C10,B5:D8,3,1)`

- Finally, press
**Enter**key to get the required result.

## Things to Be Considered

- The
**VLOOKUP**function will select the nearest match when it can’t find an exact match for the search key in a sorted data range. - The combination of
**INDEX**and**MATCH**functions will work only if we provide a sorted date range. - The
**LOOKUP**function will select the value for which it will find the last match. - The
**VLOOKUP**function can’t execute properly when it can’t find an exact match in an unsorted dataset. - Remember to format both the lookup value and range as a date.

## Conclusion

This concludes our article to learn how to apply **VLOOKUP** by date in Google Sheets. I hope the described examples were sufficient for your requirement. Feel free to leave your thought on the article in the comment box. Visit our website **OfficeWheel.com** for more helpful articles.