Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return previous date from list
In columns A & B, I have a list of dates and values, such as:
1/1/1998 12.7 3/10/1998 10.4 7/24/1998 14.6 8/3/1998 7.2 8/17/1998 42.4 12/10/1998 3.3 2/20/1999 12.7 Is there a formula that, upon entering a date, will look up the value corresponding to the next date in the list (if there's no exact match)? For example, if I enter 3/24/1998 in, say, cell D1, the formula would return 14.6. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return previous date from list
hi Barbetta,
try, =index(b1:b7,match(d1,a1:a7,1)+1) hth regards from Brazil Marcelo " escreveu: In columns A & B, I have a list of dates and values, such as: 1/1/1998 12.7 3/10/1998 10.4 7/24/1998 14.6 8/3/1998 7.2 8/17/1998 42.4 12/10/1998 3.3 2/20/1999 12.7 Is there a formula that, upon entering a date, will look up the value corresponding to the next date in the list (if there's no exact match)? For example, if I enter 3/24/1998 in, say, cell D1, the formula would return 14.6. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return previous date from list
hi Barbetta,
try, =index(b1:b7,match(d1,a1:a7,1)+1) hth regards from Brazil Marcelo " escreveu: In columns A & B, I have a list of dates and values, such as: 1/1/1998 12.7 3/10/1998 10.4 7/24/1998 14.6 8/3/1998 7.2 8/17/1998 42.4 12/10/1998 3.3 2/20/1999 12.7 Is there a formula that, upon entering a date, will look up the value corresponding to the next date in the list (if there's no exact match)? For example, if I enter 3/24/1998 in, say, cell D1, the formula would return 14.6. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return previous date from list
Try something like this:
D1: (a date to find) E1: =IF(ISNA(VLOOKUP(D1,A1:A7,B1:B7,0)),INDEX(B1:B7,MA TCH(D1,A1:A7,1)+1),VLOOKUP(D1,A1:B7,2,0)) If the formula finds an exact match in Col_A, it uses the respective value from Col_B. Otherwise, it pulls the Col_B value from the next row. Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: In columns A & B, I have a list of dates and values, such as: 1/1/1998 12.7 3/10/1998 10.4 7/24/1998 14.6 8/3/1998 7.2 8/17/1998 42.4 12/10/1998 3.3 2/20/1999 12.7 Is there a formula that, upon entering a date, will look up the value corresponding to the next date in the list (if there's no exact match)? For example, if I enter 3/24/1998 in, say, cell D1, the formula would return 14.6. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return previous date from list
Assuming that A2:B8 contains the data, try...
=INDEX(B2:B8,MATCH(D2,A2:A8)+(LOOKUP(D2,A2:A8)<D2 )) To return 12.7 for dates earlier than 1/1/98, try the following formula instead... =IF(D2<"",IF(D2A2,INDEX(B2:B8,MATCH(D2,A2:A8)+(L OOKUP(D2,A2:A8)<D2)),B 2),"") Hope this helps! In article .com, wrote: In columns A & B, I have a list of dates and values, such as: 1/1/1998 12.7 3/10/1998 10.4 7/24/1998 14.6 8/3/1998 7.2 8/17/1998 42.4 12/10/1998 3.3 2/20/1999 12.7 Is there a formula that, upon entering a date, will look up the value corresponding to the next date in the list (if there's no exact match)? For example, if I enter 3/24/1998 in, say, cell D1, the formula would return 14.6. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return previous date from list
Thanks everyone!
Domenic wrote: Assuming that A2:B8 contains the data, try... =INDEX(B2:B8,MATCH(D2,A2:A8)+(LOOKUP(D2,A2:A8)<D2 )) To return 12.7 for dates earlier than 1/1/98, try the following formula instead... =IF(D2<"",IF(D2A2,INDEX(B2:B8,MATCH(D2,A2:A8)+(L OOKUP(D2,A2:A8)<D2)),B 2),"") Hope this helps! In article .com, wrote: In columns A & B, I have a list of dates and values, such as: 1/1/1998 12.7 3/10/1998 10.4 7/24/1998 14.6 8/3/1998 7.2 8/17/1998 42.4 12/10/1998 3.3 2/20/1999 12.7 Is there a formula that, upon entering a date, will look up the value corresponding to the next date in the list (if there's no exact match)? For example, if I enter 3/24/1998 in, say, cell D1, the formula would return 14.6. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return a value between date ranges | Excel Worksheet Functions | |||
Date of the First day of the previous month | Excel Discussion (Misc queries) | |||
Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) | |||
Drop list affected by previous choice | Excel Worksheet Functions | |||
Roll back to previous date | Excel Worksheet Functions |