# VLOOKUP with Multiple Criteria Including Date Range in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates 2 well-explained examples to VLOOKUP a value that fulfills multiple criteria including a date range in Excel. We frequently use the VLOOKUP function to vertically search for a value in the dataset applying different search criteria.

## How to Use VLOOKUP with Multiple Criteria Including a Date Range: 2 Examples

In this article, we’ll use the following dataset. The dataset contains prices of three different products during 3 quarters of the year 2021. From this dataset, we want to know the price of a product at a specific date. Let’s follow the examples below to find out how to solve the above problem.

### 1. VLOOKUP Multiple Criteria Including Date Range Using the INDEX and MATCH Functions

To find out the price of the product Strawberry on the date 8/15/2021, put the following formula in cell I5.

`=INDEX(\$E\$5:\$E\$13,MATCH(1,((\$B\$5:\$B\$13=G5)*(\$D\$5:\$D\$13>=H5)*(\$C\$5:\$C\$13<=H5)),0))` The result is 21 which is shown in the highlighted row of the dataset in the above screenshot.

Formula Breakdown:
The INDEX function is used to find out the value of a specific location in a dataset. In this example,  we used the MATCH function with the INDEX function. The MATCH function provides the location of the cell (row number) that fulfills both of the criteria.
The INDEX function takes the following arguments:
(array, row_num, [col_num])
The first argument of the INDEX function is \$E\$5:\$E\$13 which represents the price column from where we need to find the desired value. Now to provide the row number argument of the INDEX function, we used the MATCH function. The MATCH function checks the conditions we specified and then outputs the row number of the cell data that fulfills both of the conditions. The MATCH function checks three conditions here-
(\$B\$5:\$B\$13=G5) checks the value Strawberry (G5) in column B.
(\$D\$5:\$D\$13>=H5)*(\$C\$5:\$C\$13<=H5) checks whether the date 8/15/2021 lies in between the Start Date and End Date dates for the rows that already met the first condition. The following screenshot shows some more outputs to understand the example better. Here we tested the formula with a product name “Banana” which doesn’t belong to the dataset and the output is an #N/A error.

### 2. VLOOKUP Multiple Criteria Including Date Range Using the XLOOKUP Function

We can also find the value of Strawberry’s price on a specific date using the XLOOKUP function. Write the following formula in cell I5.

`=XLOOKUP(1,(H5>=\$C\$5:\$C\$13)*(H5<=\$D\$5:\$D\$13)*(\$B\$5:\$B\$13=G5),\$E\$5:\$E\$13,"NotFound")` The output is 21 that lies in the highlighted row in the dataset.

Formula Breakdown:
The XLOOKUP function is used to lookup for values in a range or array.  It takes several arguments-
In this formula, the lookup_array argument represents an array that fulfills three conditions.
(H5>=\$C\$5:\$C\$13)*(H5<=\$D\$5:\$D\$13) checks whether the date 8/15/2021 lies in between the date range.
(\$B\$5:\$B\$13=G5) checks the value Strawberry (G5)) in column B and returns the array that already fulfilled the first two conditions.
We also have a string “NotFound”, in case the lookup array doesn’t meet the conditions and return an empty array.
The following screenshot shows some more outputs to understand the example better. We also put a product named “Banana” and the formula returned NotFound as our dataset doesn’t contain the product.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

1. Reply I can set up what I want using your steps above but if I have multiple strawberrys in the criteria range all with different dates, it always returns the first Strawberry price that it finds in the table – how can I return the correct price against the correct strawberry based on it’s date?

• Reply Hi, I need to use vlookup with additional date condition which looks for date of lookup value in another column and return value only if both conditions are met. Please advise formula for it

• Reply Hello, KASHIF!
Thank you for your query. Regarding your query, if you want to return value for meeting date condition only, you can use the INDEX-MATCH combination with the following formula.

`=IFERROR(INDEX(\$E\$5:\$E\$13,MATCH(H5,\$D\$5:\$D\$13,0)),"Not Available")`

If you want to meet the Product criteria and also the Date criteria, you can use the formula below.

`=IFERROR(INDEX(\$E\$5:\$E\$13,MATCH(1,((\$B\$5:\$B\$13=G5)*(\$D\$5:\$D\$13=H5)),0)),"Not Available")`

I hope your problem is resolved now.

Regards,
Tanjim Reza

• Reply Hi, JO JONES!
Can you please tell me what you meant by the multiple strawberries in the criteria range? Does it mean that you are putting multiple strawberry categories in the range? If yes, then I would suggest you put the full name of the strawberries along with their categories in the cells. As a result, every strawberry name will be unique as per dates. Moreover, if a category has multiple data at multiple dates, then you can look up your value through the unique dates.

Regards,
Tanjim Reza Advanced Excel Exercises with Solutions PDF  