| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to check for a date range match in one column and then count thevalues equal to in another | Excel Worksheet Functions | |||
| Can Index/Match pull lookup_value from a combo box? | Excel Discussion (Misc queries) | |||
| index and match on 2 largest value | Excel Worksheet Functions | |||
| match two columns (with equal dates) and their attached datasets | Excel Discussion (Misc queries) | |||
| Next largest value that is greater than lookup_value. | Excel Worksheet Functions | |||
Linear Mode
