Approximate match of date and exatc match of string in Excel

Question

Is it possible to get the following results using formulas prior to Excel 365?

It's like an index-match combo with two criteria. Argument for Column1 must have an exact match, while for Column2 it must look for the next previous date for the match found in Column1.

I could use an UDF in case this can't be done with regular formulas.

Answer 1

A Tricky Lookup (Old Excel)

• Your description is kind of misleading but your screenshot shows that you need an exact match in both columns and then find the previous match for the first column to return the value in the third column.

=IFERROR(LOOKUP(2,1/($A$2:INDEX($A:$A,MATCH(1,($A$2:$A$11=E2)*($B$2:$B$11=F2),0)+ROW($A$2)-2)=E2),$C$2:INDEX($C:$C,MATCH(1,($A$2:$A$11=E2)*($B$2:$B$11=F2),0)+ROW($A$2)-2)),"")