Getting the last value when conditions are met

[chuchuberthelot]

Hi I'm trying to get the last value when the following conditions are met.

1. Customer ID = Customer ID
2. Date 1 < Date 2

Value Returned is what I want to have.

I've tried this formula, but it doesn't seem to work...

=IF(AND(E2=$A$2:$A$13,B2:B13<F2),$C$2:$C$13)

Can anyone help me?

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=INDEX($C$2:$C$20,AGGREGATE(14,6,(ROW($C$2:$C$20)-ROW($C$2)+1)/($A$2:$A$20=E2)/($B$2:$B$20<=F2),1))

 

[jasonb75]

This should do it.

=LOOKUP(2,1/($A$2:$A$20=E2)/($B$2:$B$20<=F2),$C$2:$C$20)

Note that this will return the last in the list that meets the criteria, not the most recent date if the sort order is changed.