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

The following dataset will be used. 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.

Example 1 – VLOOKUP Multiple Criteria Including Date Range Using the INDEX and MATCH Functions

To find the price of Strawberry on 8/15/2021, input the following formula in cell I5.


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

The result is 21 as shown in the screenshot above.

Formula Breakdown:

The INDEX function is used to findthe 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

To provide the row number argument of the INDEX function, we use the MATCH function. The MATCH function checks the conditions specified and then outputs the row number of the cell data that fulfills both 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

We also tested the formula with the product name “Banana”. This product doesn’t belong to the dataset which is why the output is an #N/A error.

Example 2 – VLOOKUP Multiple Criteria Including Date Range Using the XLOOKUP Function 

Lets find the value of Strawberry’s price on a specific date using the XLOOKUP function. Enter the following formula in cell I5.


VLOOKUP Multiple Criteria Date Range

As you can see in the image above, the price was 21,

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 returns an empty array.
The following screenshot shows some more outputs to understand the example better.

We also added 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 


  • 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.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

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

      • 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.

        Tanjim Reza

    • 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.

      Tanjim Reza

Leave a reply

Advanced Excel Exercises with Solutions PDF