How to Use INDEX MATCH with Multiple Criteria for Date Range

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Use INDEX MATCH with Multiple Criteria for Date Range: 3 Easy Ways

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.


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 vs INDEX-MATCH in Excel


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 and AGGREGATE functions. 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: How to Sum Multiple Rows Using INDEX MATCH Formula


Download Excel Workbook


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

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

6 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!

  4. I really found your walkthrough enlightening, but I couldn’t get it to work for my needs. I’m trying to take data in this format
    https://i.imgur.com/Fi2Ezfn.png

    and extract it to this format
    https://i.imgur.com/KivASn6.png

    At this point it’s going to be faster to do it manually, but would really like to learn how to do something like this. I tried your xarray formula where I used an offset command to build the range for the final lookup manually, but i couldn’t get it to work

    • Hello KLC,
      Thank you for your feedback. Unfortunately, we’re having trouble accessing the pictures in the link, so you can attach your Excel workbook and send it to us at [email protected].
      Error when accessing link
      Regards,
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo