How to Use INDEX MATCH with Multiple Criteria for Date Range
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:
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.
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 1st 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 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.
- 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
Get FREE Advanced Excel Exercises with Solutions!
MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio
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