Let’s say we have certain products whose prices have remained stable for a certain period of time. We want to** INDEX MATCH** the prices for the given criteria.

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

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 the result cell (i.e.,
**I5**). As the formula in an array formula, Press**Ctrl + Shift + Enter**to apply it.

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

The formula returns the price of the Produce if the listed date falls in the given period of time (i.e., **Date** range) as depicted below.

** Formula Breakdown: **

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 mentioned 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 case we have multiple products to extract their price from the dataset, you can drag the formula down to autofill the cells.

** **

**Method 2 – XLOOKUP Function to Deal with Multiple Criteria**

The **XLOOKUP** function is only available in **Excel 365**. 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**and 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 respective price of the product that satisfies the given criteria (i.e., **Product** and **Date**).

** **

** Formula Breakdown:**

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 indicated the assigned criteria in colored rectangles as depicted in the following image.

** **

The formula displays **Not Found** if the given date criteria don’t expand within the given date range.

**Method 3 – Use 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 each week or even day. We have the prices of a certain product in a week’s interval and want to find the price for the given dates.

**Steps:** Type the following formula in the result cell (i.e., **E8**) and hit **Enter**.

`=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 product dated

**02-15-22**to

**02-25-22**is

**$0.84**. There may be more hits in the table depending on the date range, but this function will fetch the first recorded instance, if any exist.

** Formula Breakdown:**

The syntax of the **AGGREGATE** function is

`AGGREGATE (function_num, options, ref1, ref2)`

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. The**IFERROR**function results in blank cells if the formula encounters any errors.

**Download Excel Workbook**

**<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel**

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