# VLOOKUP Date Range and Return Value in Excel: 4 Methods

Letâ€™s introduce todayâ€™s dataset where the name of the Items is provided along with Order Dates, Unit Price, Quantity and Sales.

### Method 1 – VLOOKUP a Date within Date Range and Return Value

From the sample dataset, let’s say the lookup date within the date range (i.e. Order Dates) is in the D14 cell. Then, you want to return the value of the Sales of the corresponding cell (Lookup Order Date).

Use the following formula in the D15 cell:

`=VLOOKUP(D14,B5:F12,5,TRUE)`

Here, D14 is the lookup order date, B5:F12 is the table array, 5 is the column index number (i.e. going to the fifth column to the right of the match result), and finally TRUE is for approximate matching.

### Method 2 – Find a Single Output Dealing with Two Dates

Let’s saw we have to find sales between two dates.

• Insert the following formula in the C15 cell:

`=INDEX(F5:F12,MATCH(1,IF(B5:B12>B15,IF(B5:B12<B16,1)),0))`

Here, F5:F12 is the cell range for the Sales data, B5:B12 is the cell range for Order Dates, B15 is a date within the date range and B16 is another date within the date range.

In the above formula, the IF logical function returns 1 if the cell fulfills the criteria (greater than but less than). Next, the MATCH function provides the location of the matched values. Finally, the INDEX returns the value of the Sales that fulfills all criteria.

• When you press Enter, youâ€™ll get the following output.

Note: If you want to use this method for a specific date within the date range, you can find that also. In that case, you have to insert the same date instead of the second date.

### Method 3 – VLOOKUP Date Range with Multiple Criteria and Return Multiple Values

Step 01: Specifying the Start and End Dates

Initially, you have to specify the Start Date and End Date. In such a situation, using the Name Manager might be useful for updating the data frequently.

• Type two dates in two different cells as shown in the following picture.

• Select the C5 cell which shows the Start Date, and choose the Name Manager from the Formulas tab.
• Click on the New option.

• Input the name as Start_Date.
• Repeat the same process for D5 (in the example) and name it End Date.

Step 02: Dealing with the Multiple Criteria of the Date Range

• Put the following formula into the first result cell:

`=IF(AND(D5>=Start_Date,D5<=End_Date),"Yes","No")`

Here, AND function returns dates that fulfill two criteria. Furthermore, if the criteria are fulfilled, the IF function returns Yes. Else, it will return No. The cell D5 contains the order date you’re comparing.

Step 03: Counting the Lookup Value

• The following combined formula utilizes the IF and COUNTIF functions to count the lookup value if the cell fulfills criteria (matches Yes). Else, it will return 0:

`=IF(C5="Yes",COUNTIF(\$C\$5:C5,"Yes"),0)`

Here, C5 is the starting cell of the Lookup field.

Step 04: Returning Multiple Values

• Copy the name of all fields (not the values) in the previous step except the Fill Criteria.
• Enter the lookup value sequentially in the Lookup# field.

• Go to the C15 cell and insert the following formula.

`=VLOOKUP(\$B15,\$B\$4:\$H\$12,MATCH(C\$14,\$B\$4:\$H\$4,0),FALSE)`

\$B15 is the value of the Lookup# field, \$B\$4:\$H\$12 is the table array, C\$14 is the lookup value, \$B\$4:\$H\$4 is the lookup array, 0 is for the exact matching. TheÂ MATCH function finds the column index number actually for the VLOOKUP function. Finally, the VLOOKUP function returns a matched value of the Order Dates.

You have to specify a fixed reference with the dollar sign (\$) carefully, or you won’t get the desired output.

• Press Enter, and youâ€™ll get the output is 44715. This is how Excel stores dates in a numerical format.
• Drag the plus sign to the adjacent columns until the Sales and the below cells until the lookup value is 5 (use the Fill Handle Tool).

• You should get the following output.

• Select the cell range C15:C19 and press CtrlÂ + 1 to open the Format Cells option.

• Choose the Date format.

### Method 4 – VLOOKUP Two Date Ranges Using the LOOKUP Function

For this example, we have added an individual column namely Delivery Date and need to find the specific item that meets both given dates.

Insert the following formula into the result cell:

`=LOOKUP(2,1/(\$B\$5:\$B\$12<=C15)/(\$D\$5:\$D\$12>=C16),\$C\$5:\$C\$12)`

Here, \$B\$5:\$B\$12 is the cell range of the Order Dates, \$D\$5:\$D\$12 is the cell range for the Delivery Dates, C15 is an order date and C16 is delivery date. Finally, \$C\$5:\$C\$12 is the cell range for the Items.

After inserting the formula, youâ€™ll get the following output.

## Related Articles

<< Go Back to Date Range | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

1. Hello,

Great article – I have a similar issue that I don’t think is quite captured here that I believe you may be able to help with. I work with logistics and often we have a trucker awarded a certain rate based on a destination. However, these rates have changed quite frequently over the past few months and I was wondering if it were possible to return a price for a given trucker/destination combo based on a given date.

Example below:

List of Orders
5/31/22 Trucker A to Destination B – Price \$250
6/1/22 Trucker A to Destination B – Price \$300
6/5/22 Trucker A to Destination B – Price \$400

If I have a range of orders from 5/1/22-6/5/22, how can my formula return the latest price based on the dates of the orders? Therefore, an order from 6/2/2022 would return \$300 based on Date/Trucker/Destination.

Any help greatly appreciated!

2. List of Orders
5/31/22 Trucker A to Destination B Price \$250
6/01/22 Trucker A to Destination B Price \$300
6/05/22 Trucker A to Destination B Price \$400
First, sort your Order Date column by Newest to Oldest order. Then, use Trucker A to Destination B as the lookup value. Now, you’ll get the latest price by using the VLOOKUP function.

3. Method #2 was the solution that I had been looking for for a couple of hours. Literally saved my butt. I had looked everywhere for that. Thanks so much Abdul.

Advanced Excel Exercises with Solutions PDF