Looking for last entry using multiple criteria including date

Hi,

I'm looking for the previous entry in column j and placing it in column h of the present row using date(coulumn b) and team(column c) criteria.
aaaaaBaaaaCaaaaHaaJ
2/4/2003 Team A 45 55
2/4/2003 Team B 67 53
2/4/2003 Team C 12 23
9/4/2003 Team A 55 38
9/4/2003 Team B 67 66
9/4/2003 Team C ?

Question mark would read 23.

Originally Posted by FDibbins

Hi and welcome to the forum

I would do this using a helper column. (I used A, youe what you want and adjust the references accordingly) - you can hide the helper if needed....
in A, copued down (assuming your data starts in row 2...
=B2&C2

Then use this to find the value you want...
=INDEX(J2:J6,MATCH(MAX(IF(C2:C6=C7,B2:B6,0))&C7,A2:A6,0),1)
note: that is an array formula and must be entered using CRTL SHIFT enter, not just enter

Hope that helps

Thanks, getting n/a when I enter formula.