match largest value that is exactly equal to lookup_value
 

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

match largest value that is exactly equal to lookup_value
 

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

"birchin" wrote:

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

match largest value that is exactly equal to lookup_value
 

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)

--
Biff
Microsoft Excel MVP


"birchin" wrote in message
news:rP8tj.6514$rh.5302@trnddc02...
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