Excel MATCH with multiple criteria (not exact match)-CodePudding

I have been tinkering with various Excel functions (MATCH/INDEX, VLOOKUP, SUMPRODUCT, AGGREGATE, etc.) to deliver the result I need, but without success. So I'm hoping someone can tell me what the best way to do this is.

From my sample data below I need a formula that will return the row number accurately when I provide the exact code (exact match with column A) along with a date that falls between the dates listed in column B. I started with a formula that does work when both items match exactly -- i.e.,

=MATCH(1,(("B"=A:A)*(2005=B:B)),0)

will return row 6, which is correct. But I cannot figure out how to tweak that so it works when I search for Code=B and Year=2007. In this case I want it to return row 6 -- where Code=B and the year is the closest / next lower value. My closest attempt (which does not work) is:

=SUMPRODUCT(MATCH(1,(A:A="B")*(B:B<=2007),0))

Any help appreciated!

CodePudding user response：

Use AGGREGATE:

=AGGREGATE(14,7,ROW($A$2:$A$7)/((D1=$A$2:$A$7)*($B$2:$B$7<=D2)),1)

CodePudding user response：

Perhaps you could try using LOOKUP() Function instead of MATCH()

• Formula used in cell E5

=LOOKUP(2,1/((E2=$A$2:$A$7)*($B$2:$B$7<=E3)),ROW($B$2:$B$7))

CodePudding user response：

Updated

If you have Excel 2019 or better, this should work.

=SUBSTITUTE(RIGHT(  INDEX( SORT(FILTER(ABS(B:B-E2)&REPT("