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!~

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!~

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!~

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!~

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!~

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!~

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!~

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!~

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!~

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!~