INDEX / MATCH with multiple criteria including a date range

I'm trying to pull prices based on whether the code matches, and if the date is within a certain range.

I want to pull column H based on these conditions:

I figured an array formula with INDEX/MATCH would be the best method, but have not been successful so far.

I've been attempting a formula using logic similar to this:

{=INDEX(H2:H10, MATCH(1, IF(A2=F2:F10,IF(B2<=G2:G10,IF(C2>=G2:G10,1),0),0),0))}

2 Answers

If you want the first then use this array form of INDEX:

=INDEX($H$2:$H$10,MATCH(1,(($F$2:$F$10=A2)*($G$2:$G$10>=B2)*($G$2:$G$10<=C2)),0))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

If you want all the matching prices, you can use the TEXTJOIN() function:

Array enter the following formula in D3 and copy-paste/fill down the column:

{=TEXTJOIN(", ",TRUE,IFERROR(1/(1/(($F$2:$F$10=A2)*($G$2:$G$10>=B2)*($G$2:$G$10<=C2)*($H$2:$H$10))),""))}

Note that TEXTJOIN() is only available in Excel 2016. In earlier versions of Excel, a poly-fill UDF is required. (See this post for a basic one.)