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.

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

**Table of Contents**hide

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

**đź”„ 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**,

**, andÂ**

`$D$5:$D$16>=H5`

**declare the criteria. To provide better identification, we color respective ranges in rectangles.**

`$C$5:$C$16<=H5`

** **

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

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

** **

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.

** **

**đź”„ 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.

** **

**âž¤ **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.

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

**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))),"")`

** **

The **1 ^{st} Â **price of the certain product date

**02-15-22**to

**02-25-22**is

**$0.84**. There may be a

**2**or

^{nd}**3**price available but at first, we stick to the

^{rd}**1**one.

^{st}**đź”„ 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

**n**smallest index number of a row that satisfies the given criteria.

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

âž¤ 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.

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