We use **VLOOKUP** by date to find dates in a data table. It is very useful to find the values corresponding to particular dates from a dataset. The value we lookup for in using the **VLOOKUP function** is called the **lookup value**. Therefore, the date is the lookup value when we apply VLOOKUP by date. This article shows how to apply **VLOOKUP** by date in the simplest way. The following picture gives an idea of how **VLOOKUP** by date works.

**Download Practice Workbook**

You can download the practice workbook from the download button below.

**Apply VLOOKUP by Date in Excel**

We are going to use the following dataset to illustrate how to apply **VLOOKUP** by date in excel. The dataset contains the sales amounts corresponding to different dates.

Assume, you want to enter the date in cell **E5** to find the corresponding amount of sales in cell **F5**.

You can easily do that by following the steps below.

**Steps**

1. First, enter the following formula in cell **F5**:

`=VLOOKUP(E5,B5:C10,2,FALSE)`

2. Don’t worry about the **#N/A** error.

3. Now, enter a date in cell **E5** to get the desired sales amount as follows.

4. Be cautious about the date format while entering a date. You must enter the date in the same format as in the dataset which is **MM/DD/YYYY** in this case.

5. If the date you are looking for doesn’t exist in the dataset, it will show a **#N/A** error. For example, enter 2/4/2022 instead of 2/4/2022 and you will see the following.

**How does the Formula Work?**

1. The VLOOKUP function consists of the following arguments:

`VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

2. First, it asks for the value to lookup(**lookup_value)**. Then the range of data to look up(**table_array)**. After that, it asks the **column number**(**col_num_index**) where the return value exists in that range. Finally, the **range_lookup** argument asks whether you want to look for an **Approximate Match(TRUE)** or an **Exact Match(FALSE)**.

3. If we compare this with the formula entered in cell **F5** then we get,

- Lookup_value =
**E5**which contains the date we look for. - Table_array =
**B5:C10**which contains the dataset. - Col_index_num =
**2**which is the second column of the dataset containing the sales amounts we wanted to find out. - Range_lookup =
**FALSE**, meaning looking for an exact match.

**Read More: How to Use VLOOKUP to Find a Value That Falls Between a Range**

**Perform VLOOKUP to Return Date in Excel**

Now, what if we want to find the date corresponding to the maximum sales amount? Let’s follow the steps below for that.

**Steps**

1. First, enter the following formula in cell **E5**:

`=MAX(C5:C10)`

2. Then the **MAX function** in this formula will return the maximum sales amount from the **Sales** column.

3. After that, enter the following formula in cell **F5**:

`=VLOOKUP(E5,B5:C10,1,FALSE)`

4. But this gives the **#N/A** error.

5. It is because the value we are looking for remains in the second column in the table array.

6. To fix this problem, let’s interchange the **Date **and the **Sales** columns.

7. Then change the formula in cell** E5** to the following one:

`=MAX(B5:B10)`

8. The new dataset will look as follows.

9. After that, apply the following formula in cell **F5**:

`=(VLOOKUP(E5,B5:C10,2,FALSE)`

10. This will return the date as a number.

11. The simplest way to fix this problem is by changing the date format of that cell from the **Home **tab.

12. An alternative way to do that is by applying the following formula instead of the earlier one in cell **F5**.

`=TEXT(VLOOKUP(E5,B5:C10,2,FALSE),"D-MMM-YY")`

13. The **TEXT function **in this formula will change the value obtained from the **VLOOKUP** formula to date format.

**Read More: Lookup Value in a Range and Return in Excel (5 Easy Ways)**

**Things to Remember**

- You must enter dates in the same format as the dates are in the dataset.
- The lookup value must be in a column earlier than the column which contains the return value.
- Make sure the text format is inside double quotes (
**“”**) in the**TEXT**function.

**Conclusion**

Now you know how to apply **VLOOKUP** by date in excel. Please use the comment section below for further queries or suggestions. You may visit our **ExcelDemy** blog to learn more about excel.