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.


Download Practice Workbook

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


2 Examples of Using VLOOKUP with Multiple Criteria Including a Date Range

In this article, we’ll use the following dataset. The dataset contains prices of three different products during 3 quarters of the year 2021.

VLOOKUP Multiple Criteria Including Date Range

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))

D:\softeko\1970_37-0032_VLOOKUP Multiple Criteria\01\vlookup-multiple-criteria-including-date-range (3).png

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.

VLOOKUP Multiple Criteria Date Range

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.

VLOOKUP Multiple Criteria Date Range

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.

Read More: Excel VLOOKUP with Multiple Criteria in Column and Row


Similar Readings


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")

VLOOKUP Multiple Criteria Date Range

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 in Excel (6 Examples)


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.

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

Al Arafat Siddique

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.

2 Comments
  1. 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?

    • Hi, JO JONES!
      Thank you for your query.
      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.
      If your problem still exists, please let us know your feedback and help us understand your question better.

      Regards,
      Tanjim Reza

Leave a reply

ExcelDemy
Logo