Index Match with multiple criteria including date range

First,

H6 is wrong as it only filter the date instead of both criteria.
you can try change the D in B6 to C.
the result is C instead of error.

it should be
=INDEX(B4:B9,SUMPRODUCT((C4:C9=H4)*(ROW(B4:B9)-3)*(B4:B9=H3)))

in H19
=INDEX(B4:B9,SUMPRODUCT((C4:C9>=I16)*(C4:C9<=I17)*(ROW(B4:B9)-3)*(B4:B9=H14)))


PS: it may not suitable as it ONLY function well when there have ONLY ONE RESULT that meet all criteria.

Thank you for your reply. This formula worked, but I inadvertently miscommunicated one of the required criteria. I actually need to show if the the date of the promotion falls within a specific month then it uses a "date range" search for that month. For example, if I add a criteria of 11/1/18 it will search for the letter "D" that falls in that month. I hope that makes sense.

I also need one more argument to avoid errors if there is not a match. I tried to include an iferror argument in your formula but it would not work.
Here is what I added, that did not work. =iferror(INDEX(B4:B9,SUMPRODUCT((C4:C9>=I16)*(C4:C9<=I17)*(ROW(B4:B9)-3)*(B4:B9=H14))))

By the way, I did not realize that the solution was a combination of Index and SumProduct, as I was thinking it was Index and Match. I was also thinking it would require an array formula given the required date range. I do have one additional question. Is there a way to modify the formula so if a row is added above the header (row 3) that the number will dynamically change from -3 to -4? I noticed if I add a row the result will be incorrect.

By the way, my desired answer will always only be one option so this formula will meet the criteria, except if there is no result as mentioned above.

Perk,

Try changing BW's formula to this.

=INDEX(B4:B9,SUMPRODUCT((C4:C9=H4)*(ROW(B4:B9)-MIN(ROW(B4:B9))+1)*(B4:B9=H3)))

Now insert the row(s). It still works at my end.

There is nothing "wrong" with the syntax of your IFERROR. The approach BW has used will return 0 in the SUMPRODUCT if no match is found.

I would propose an alternative array formula.

This version needs to be array entered (Ctrl + Shift + Enter)

Formula:

Please Login or Register  to view this content.

If you are interested this variation of same does not have to be committed CSE. By inserting an additional INDEX call:

Formula:

Please Login or Register  to view this content.

You are welcome. Thank you for the feedback and marking your thread SOLVED. It helps everyone.

Good work.

let me explain abit on this
(ROW(B4:B9)-3)

I use this is due to the
=ROW(B4:B9)
will return to
={4,5,6,7,8,9}

it will not suitable for INDEX function,
=index(,"Here",)
as it start at 4,

hence I minus 3 (ROW(B4:B9)-3) to get the result of
={1,2,3,4,5,6}

hope it clarify.