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

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.

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.

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-
(lookup, lookup_array, return_array, [not found])
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.

Read More:  VLOOKUP with Multiple Criteria and Multiple Results 

Notes

• The XLOOKUP function is a well-suited and more flexible alternative to the VLOOKUP and HLOOKUP It provides match_mode and search_mode two different arguments with multiple options that make a lookup for values easy and flexible with different conditions.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Now, we know how to VLOOKUP with multiple criteria including date range. Hopefully, it would encourage you to use this more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• Excel VLOOKUP with Multiple Criteria in Horizontal & Vertical Way
• Excel VLOOKUP with Multiple Criteria in Column and Row
• Vlookup with Multiple Criteria without a Helper Column in Excel 
• How to Use VLOOKUP with Multiple Criteria in Different Columns
• How to Apply VLOOKUP with Multiple Criteria Using the CHOOSE Function
• How to Use VLOOKUP with Multiple Criteria in Different Sheets

<< Go Back to VLOOKUP with Multiple Criteria | Excel VLOOKUP Function | Excel Functions | Learn Excel