match largest value that is exactly equal to lookup_value

[birchin]

Haw to finds the not first value that is exactly equal to lookup_value.
lookup_value E2=2
lookup_array B2:B9

formula in F2=INDEX(B2:B9,MATCH(E2,C2:C9,0)) :

how i can return largest value located in cel B9 (01/9/08)?

B C E F
2 1/2/2008 1 2 1/4/08
3 1/3/2008 1
4 1/4/2008 2
5 1/5/2008 2
6 1/6/2008 2
7 1/7/2008 2
8 1/8/2008 2
9 1/9/2008 2

 

[yshridhar]

Instead of E2 in Match, put Max(c2:c9)
with regards
sreedhar

 

[T. Valko]

Based on you sample dates being in ascending order:

=LOOKUP(2,1/(C2:C9=E2),B2:B9)

Format as DATE

If your dates are in random order try this array formula** :

=MAX(IF(C2:C9=E2,B2:B9))

Format as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)