How to Use INDEX MATCH with Multiple Criteria for Date Range

Variables (such as commodity price, share, interest rate, etc.) subject to dates cause general people to panic, as their prices depend on dates. INDEX MATCH multiple criteria date range can extract prices from a given date range.

Let’s say we have certain products with their prices stable for a certain period of time. And we want to INDEX MATCH the prices for the given criteria.

dataset-Index Match Multiple Criteria Date Range

In this article, we use multiple functions to INDEX MATCH multiple criteria date range.


Download Excel Workbook


3 Easy Ways to Use INDEX MATCH for Multiple Criteria of Date Range

Method 1: Using INDEX MATCH Functions for Multiple Criteria of Date Range

We want to extract the price for a certain product on a specific date. Suppose we want to see the price of an Ice Cream on 02-10-22 (month-day-year). If the given date falls between the offered period of time, we’ll have the price extracted in any blank cell.

Steps: Insert the following formula in any blank cell (i.e., I5). As the formula in an array formula, Press CTRL+SHIFT+ENTER to apply it. Instantly the formula returns the Product price if it falls in the given period of time (i.e., Date range) as depicted below.

=INDEX($E$5:$E$16,MATCH(1,(($B$5:$B$16=G5)*($D$5:$D$16>=H5)*($C$5:$C$16<=H5)),0))

index match-Index Match Multiple Criteria Date Range

🔄 Formula Autopsy:

Excel INDEX function finds a value of a given location within a given range. In our case, we use the MATCH function induced with the INDEX function. The MATCH function passes its result as a row number for entries that satisfy given criteria. The syntax of an INDEX function is

INDEX(array, row_num, [col_num])

In the formula, $E$5$E$16 refers to the array argument. Inside the MATCH function $B$5:$B$16=G5, $D$5:$D$16>=H5, and  $C$5:$C$16<=H5 declare the criteria. To provide better identification, we color respective ranges in rectangles.

identification in rectangles

The MATCH function locates the position of a given value within a row, column, or table. As we said earlier, the MATCH portion passes the row number for the INDEX function. The syntax of the MATCH function is

MATCH (lookup_value, lookup_array, [match_type])

The MATCH portion is

=MATCH(1,(($B$5:$B$16=G5)*($D$5:$D$16>=H5)*($C$5:$C$16<=H5)),0)

The MATCH portion assigns 1 as lookup_value, ($B$5:$B$16=G5)*($D$5:$D$16>=H5)*($C$5:$C$16<=H5) as lookup_array, and 0 declares the [match_type] as an exact match.

The used MATCH formula returns 3 as it finds Ice Cream in row number 3.

identification

In cases, we have multiple products to extract their price from the dataset. It kind of looks like the following picture,

index match function result

The INDEX MATCH combined formula fetches Prices upon its arguments satisfying the criteria. Otherwise results in a #N/A error as depicted in the above screenshot.

Read More: VBA INDEX MATCH Based on Multiple Criteria in Excel (3 Methods)


Method 2: XLOOKUP Function to Deal with Multiple Criteria

Similar to Method 1, we can use the XLOOKUP function (only available in Excel 365) to INDEX MATCH multiple criteria date range. The syntax of the XLOOKUP function is

XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])

Steps: Use the below formula in cell I5 then Hit ENTER.

=XLOOKUP(1,(H5>=$C$5:$C$16)*(H5<=$D$5:$D$16)*($B$5:$B$16=G5),$E$5:$E$16,"NotFound")

The XLOOKUP formula returns the respected price that satisfies the given criteria (i.e., Product and Date) as shown in the above picture.

xlookup function-Index Match Multiple Criteria Date Range

🔄 Formula Autopsy:

The XLOOKUP assigns 1 as its lookup argument, (H5>=$C$5:$C$16)*(H5<=$D$5:$D$16)*($B$5:$B$16=G5) as lookup_array, $E$5:$E$16 as return_array. Also, the formula displays Not Found text in case entries don’t fall in the date range. We indicate the assigned criteria in colored rectangles as depicted in the following image.

identification

For multiple Products, you can apply the XLOOKUP formula and extract the prices upon satisfying the given criteria. Also, the formula displays Not Found if the given date criteria don’t expand within the given date range.

xlookup final result

You can add more criteria than used in the formula. In order to give simple and lucid scenarios, minimal criteria have been used.

Read More: XLOOKUP with Multiple Criteria in Excel (4 Easy Ways)


Method 3: INDEX and AGGREGATE Functions to Extract a Volatile Price from Date Range

Some Products prices (i.e., crude oil, currency, etc.) are so volatile that they fluctuate for weeks or even days. We have prices of a certain product in a week’s interval. We want to find the price for the given dates. To find the price for a given date range, we can use the combined INDEX AGGREGATE function. The syntax of the AGGREGATE function is

AGGREGATE (function_num, options, ref1, ref2)

Steps: Type the following formula in any blank cell (i.e., E8).

=IFERROR(INDEX(C$5:C$13,AGGREGATE(15,6, ROW(B$5:B$13)/((B$5:B$13>=F$4)*(B$5:B$13<=F$5))-ROW(B$5)+1,ROWS(E$8:E8))),"")

index aggregate function-Index Match Multiple Criteria Date Range

The 1st  price of the certain product date 02-15-22 to 02-25-22 is $0.84. There may be a 2nd or 3rd price available but at first, we stick to the 1st one.

🔄 Formula Autopsy:

In the formula, =IFERROR(INDEX(C$5:C$13,AGGREGATE(15,6, ROW(B$5:B$13)/((B$5:B$13>=F$4)*(B$5:B$13<=F$5))-ROW(B$5)+1,ROWS(E$8:E8))),"");

AGGREGATE(15,6,ROW(B$5:B$13)/((B$5:B$13>=F$4)*(B$5:B$13<=F$5))-ROW(B$5)+1,ROWS(E$8:E8))) portion provides the row number to the INDEX function. C$5:C$13 is the array argument of the INDEX function.

Inside the AGGREGATE formula,

(B$5:B$13>=F$4)*(B$5:B$13<=F$5) returns 1 or 0 depending on whether the dataset dates fall in the range or not.

ROW(B$5:B$13)/((B$5:B$13>=F$4)*(B$5:B$13<=F$5)) returns an array of row numbers depending on the satisfying the date criteria. Otherwise, results in error values.

ROW(B$5:B$13)/((B$5:B$13>=F$4)*(B$5:B$13<=F$5))-ROW(B$5)+1 as ref1 results in an array of row numbers converted into index numbers otherwise in error values.

ROWS(E$8:E8) as ref2 results in row number and it’s an easy way to get row number as you apply the formula downward.

The number 15=function_num (i.e., SMALL), 6=options (i.e., ignore error values). You can choose function_num from 19 different functions and Options from 8 different options.

At last, AGGREGATE(15,6,ROW(B$5:B$13)/((B$5:B$13>=F$4)*(B$5:B$13<=F$5))-ROW(B$5)+1,ROWS(E$8:E8))) passes the nth smallest index number of a row that satisfies the given criteria.

In case any error occurs, IFERROR(INDEX...),"") ignores all types of errors and transforms them into blanks.

identification

➤ Drag the Fill Handle to fetch other matched prices within the criteria date range. And the IFERROR function results in blank cells if the formula encounters any errors.

index aggregate final result

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


Conclusion

In this article, we demonstrate multiple ways to INDEX MATCH multiple criteria date range. We use functions like INDEX, MATCH. XLOOKUP, and AGGREGATE to form formulas that index match entries that satisfy criteria. Hope these above-mentioned ways outs help you to deal with your situation. Comment if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

4 Comments
  1. Hello, I downloaded the multiple criteria data range workbook and it works great until I click a cell with the price then click the formula and back into the price cell, I get N/A for the price result.
    Not sure if my Excel pushing the formula out of the date range.
    I have Office 2021 Home and Business version Excel.

  2. Hello Jorge,
    This is an array formula. It must be completed by pressing Ctrl+Shift+Enter. Otherwise, it won’t work. For a normal formula, we press only Enter. But for an array, you have to press Ctrl+Shift+Enter. I hope you get your solution. If you find any more problems, feel free to ask in the comment box. We are always here to help you.

  3. What if in the first table you have 2 “ice cream” rows with two different dates. If you want to extract lets say the date that is closest to the Criteria 2, how could you do this? I tried your formula but it doesn’t work as intended. It just brings the same price

    • Hello Frank,
      Thank you for sharing your query. I am replying to you on behalf of ExcelDemy. According to your question, I assume the dataset will look like this where the product “Ice Cream” is in two rows with different dates and prices.

      Now, to solve your problem, you have to put a date in Criteria 2 that lies in between the dates you want a price from. Afterward, apply the same formula as we used earlier and it will show the accurate price.
      =INDEX($E$5:$E$16,MATCH(1,(($B$5:$B$16=G5)*($D$5:$D$16>=H5)*($C$5:$C$16<=H5)),0))

      I hope this solution will help you. Let us know your feedback.
      Thanks!

Leave a reply

ExcelDemy
Logo