match largest value that is exactly equal to lookup_value

B

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
 
T

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top