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.

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

** **

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

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

** **

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.

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

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.

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.

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!

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

Regards,

ExcelDemy