| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
This should be easy - date lookup
Here is my spreadsheet:
---Date Range--- Conversion Date Descr1 Begin Date End Date Descr 10/25/94 ? 02/05/96 03/03/96 FY1997P1 12/12/94 ? 03/04/96 03/31/96 FY1997P2 10/17/95 ? 04/01/96 04/28/96 FY1997P3 10/31/95 ? 04/29/96 05/26/96 FY1997P4 11/14/95 ? 05/27/96 06/23/96 FY1997P5 Assume Conversion Date is column G, Descr1 = column h, Begin Date is column j, End Date = column k, and Descr = column l. I need to 'lookup' the conversion date within the Begin Date and End Date range, to return the value 'Descr' in the cell under 'Descr1' I have search the KB and can't seem to find how the 'VLOOKUP' function can look up a range of dates...anyone's help is greatly appreciated!~ |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
This should be easy - date lookup
it's not a lookup, just math, excel stores the dates as numbers, use an IF
statement. "Lisa" wrote: Here is my spreadsheet: ---Date Range--- Conversion Date Descr1 Begin Date End Date Descr 10/25/94 ? 02/05/96 03/03/96 FY1997P1 12/12/94 ? 03/04/96 03/31/96 FY1997P2 10/17/95 ? 04/01/96 04/28/96 FY1997P3 10/31/95 ? 04/29/96 05/26/96 FY1997P4 11/14/95 ? 05/27/96 06/23/96 FY1997P5 Assume Conversion Date is column G, Descr1 = column h, Begin Date is column j, End Date = column k, and Descr = column l. I need to 'lookup' the conversion date within the Begin Date and End Date range, to return the value 'Descr' in the cell under 'Descr1' I have search the KB and can't seem to find how the 'VLOOKUP' function can look up a range of dates...anyone's help is greatly appreciated!~ |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
This should be easy - date lookup
Based on your posted sample there are *no* conversion dates that fall within
the begin date - end date. Let's assume that your first conversion date is 4/23/1996 This array formula** will return FY1997P3: =INDEX(L2:L6,MATCH(1,(G2=J2:J6)*(G2<=K2:K6),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Lisa" wrote in message ... Here is my spreadsheet: ---Date Range--- Conversion Date Descr1 Begin Date End Date Descr 10/25/94 ? 02/05/96 03/03/96 FY1997P1 12/12/94 ? 03/04/96 03/31/96 FY1997P2 10/17/95 ? 04/01/96 04/28/96 FY1997P3 10/31/95 ? 04/29/96 05/26/96 FY1997P4 11/14/95 ? 05/27/96 06/23/96 FY1997P5 Assume Conversion Date is column G, Descr1 = column h, Begin Date is column j, End Date = column k, and Descr = column l. I need to 'lookup' the conversion date within the Begin Date and End Date range, to return the value 'Descr' in the cell under 'Descr1' I have search the KB and can't seem to find how the 'VLOOKUP' function can look up a range of dates...anyone's help is greatly appreciated!~ |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
This should be easy - date lookup
thanks, dlw...one more queston - how do you use an IF statement for a range?
"dlw" wrote: it's not a lookup, just math, excel stores the dates as numbers, use an IF statement. "Lisa" wrote: Here is my spreadsheet: ---Date Range--- Conversion Date Descr1 Begin Date End Date Descr 10/25/94 ? 02/05/96 03/03/96 FY1997P1 12/12/94 ? 03/04/96 03/31/96 FY1997P2 10/17/95 ? 04/01/96 04/28/96 FY1997P3 10/31/95 ? 04/29/96 05/26/96 FY1997P4 11/14/95 ? 05/27/96 06/23/96 FY1997P5 Assume Conversion Date is column G, Descr1 = column h, Begin Date is column j, End Date = column k, and Descr = column l. I need to 'lookup' the conversion date within the Begin Date and End Date range, to return the value 'Descr' in the cell under 'Descr1' I have search the KB and can't seem to find how the 'VLOOKUP' function can look up a range of dates...anyone's help is greatly appreciated!~ |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
This should be easy - date lookup
Thanks, dlw -
Do you know how I can use the IF statement with a range? "dlw" wrote: it's not a lookup, just math, excel stores the dates as numbers, use an IF statement. "Lisa" wrote: Here is my spreadsheet: ---Date Range--- Conversion Date Descr1 Begin Date End Date Descr 10/25/94 ? 02/05/96 03/03/96 FY1997P1 12/12/94 ? 03/04/96 03/31/96 FY1997P2 10/17/95 ? 04/01/96 04/28/96 FY1997P3 10/31/95 ? 04/29/96 05/26/96 FY1997P4 11/14/95 ? 05/27/96 06/23/96 FY1997P5 Assume Conversion Date is column G, Descr1 = column h, Begin Date is column j, End Date = column k, and Descr = column l. I need to 'lookup' the conversion date within the Begin Date and End Date range, to return the value 'Descr' in the cell under 'Descr1' I have search the KB and can't seem to find how the 'VLOOKUP' function can look up a range of dates...anyone's help is greatly appreciated!~ |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
This should be easy - date lookup
Thank, dlw...
But how do you use the "IF" statement with a range? In my example, the range I have is two columns x 145 rows.... "dlw" wrote: it's not a lookup, just math, excel stores the dates as numbers, use an IF statement. "Lisa" wrote: Here is my spreadsheet: ---Date Range--- Conversion Date Descr1 Begin Date End Date Descr 10/25/94 ? 02/05/96 03/03/96 FY1997P1 12/12/94 ? 03/04/96 03/31/96 FY1997P2 10/17/95 ? 04/01/96 04/28/96 FY1997P3 10/31/95 ? 04/29/96 05/26/96 FY1997P4 11/14/95 ? 05/27/96 06/23/96 FY1997P5 Assume Conversion Date is column G, Descr1 = column h, Begin Date is column j, End Date = column k, and Descr = column l. I need to 'lookup' the conversion date within the Begin Date and End Date range, to return the value 'Descr' in the cell under 'Descr1' I have search the KB and can't seem to find how the 'VLOOKUP' function can look up a range of dates...anyone's help is greatly appreciated!~ |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
This should be easy - date lookup
Thanks dlw,
But how do I specify a range for an IF statement? The data I am needing to reference is 2 columns x 145 rows... Does anyone know how to do this? Thanks! "dlw" wrote: it's not a lookup, just math, excel stores the dates as numbers, use an IF statement. "Lisa" wrote: Here is my spreadsheet: ---Date Range--- Conversion Date Descr1 Begin Date End Date Descr 10/25/94 ? 02/05/96 03/03/96 FY1997P1 12/12/94 ? 03/04/96 03/31/96 FY1997P2 10/17/95 ? 04/01/96 04/28/96 FY1997P3 10/31/95 ? 04/29/96 05/26/96 FY1997P4 11/14/95 ? 05/27/96 06/23/96 FY1997P5 Assume Conversion Date is column G, Descr1 = column h, Begin Date is column j, End Date = column k, and Descr = column l. I need to 'lookup' the conversion date within the Begin Date and End Date range, to return the value 'Descr' in the cell under 'Descr1' I have search the KB and can't seem to find how the 'VLOOKUP' function can look up a range of dates...anyone's help is greatly appreciated!~ |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
This should be easy - date lookup
I still can't figure out how to do the IF statement whe using a range. Can
anyone help, please? Lisa "dlw" wrote: it's not a lookup, just math, excel stores the dates as numbers, use an IF statement. "Lisa" wrote: Here is my spreadsheet: ---Date Range--- Conversion Date Descr1 Begin Date End Date Descr 10/25/94 ? 02/05/96 03/03/96 FY1997P1 12/12/94 ? 03/04/96 03/31/96 FY1997P2 10/17/95 ? 04/01/96 04/28/96 FY1997P3 10/31/95 ? 04/29/96 05/26/96 FY1997P4 11/14/95 ? 05/27/96 06/23/96 FY1997P5 Assume Conversion Date is column G, Descr1 = column h, Begin Date is column j, End Date = column k, and Descr = column l. I need to 'lookup' the conversion date within the Begin Date and End Date range, to return the value 'Descr' in the cell under 'Descr1' I have search the KB and can't seem to find how the 'VLOOKUP' function can look up a range of dates...anyone's help is greatly appreciated!~ |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
This should be easy - date lookup
How can the IF statement be used to incorporate the range to lookup the
appropriate date? Can anyone help, please? "dlw" wrote: it's not a lookup, just math, excel stores the dates as numbers, use an IF statement. "Lisa" wrote: Here is my spreadsheet: ---Date Range--- Conversion Date Descr1 Begin Date End Date Descr 10/25/94 ? 02/05/96 03/03/96 FY1997P1 12/12/94 ? 03/04/96 03/31/96 FY1997P2 10/17/95 ? 04/01/96 04/28/96 FY1997P3 10/31/95 ? 04/29/96 05/26/96 FY1997P4 11/14/95 ? 05/27/96 06/23/96 FY1997P5 Assume Conversion Date is column G, Descr1 = column h, Begin Date is column j, End Date = column k, and Descr = column l. I need to 'lookup' the conversion date within the Begin Date and End Date range, to return the value 'Descr' in the cell under 'Descr1' I have search the KB and can't seem to find how the 'VLOOKUP' function can look up a range of dates...anyone's help is greatly appreciated!~ |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
This should be easy - date lookup
How can the IF statement be used to search thgouh my range of dates and
return the appropriate info? Can anyone help me, please? "Lisa" wrote: Here is my spreadsheet: ---Date Range--- Conversion Date Descr1 Begin Date End Date Descr 10/25/94 ? 02/05/96 03/03/96 FY1997P1 12/12/94 ? 03/04/96 03/31/96 FY1997P2 10/17/95 ? 04/01/96 04/28/96 FY1997P3 10/31/95 ? 04/29/96 05/26/96 FY1997P4 11/14/95 ? 05/27/96 06/23/96 FY1997P5 Assume Conversion Date is column G, Descr1 = column h, Begin Date is column j, End Date = column k, and Descr = column l. I need to 'lookup' the conversion date within the Begin Date and End Date range, to return the value 'Descr' in the cell under 'Descr1' I have search the KB and can't seem to find how the 'VLOOKUP' function can look up a range of dates...anyone's help is greatly appreciated!~ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EASY cond format Date?? | Excel Discussion (Misc queries) | |||
| Easy for the experts: Lookup,index,match.... | Excel Worksheet Functions | |||
| How to set up easy lookup methods | Excel Discussion (Misc queries) | |||
| Should be really easy lookup ! | Excel Worksheet Functions | |||
| Help - Easy Q not sure if need IF or LOOKUP | Excel Discussion (Misc queries) | |||
Linear Mode
