want to lookup up the date value entered in cells C15 through C22

Lookup table = $C $6 through $D $11 and return value in column 2

Problem - dates in C15 through C22 are date ranges. Formula must return the
date in column D of the lookup table_array which corresponds to the date in
cells $C$6 through $C$11 that is =C15 and <= C 15

So if table looks like this

c6 = 1/1/2010 d6 = 1/12/2010
c7 = 1/15/2010 d7 = 1/29/2010
c8 = 1/31/2010 d8 = 2/13/2010
c9 = 2/15/2010 d9 = 2/26/2010
c10 = 2/28/2010 d10 = 3/13/2010
c11 = 3/15/2010 d11 = 3/29/2010

and date is entered in c ; formula in d returns
c15 = 1/31/2010 d15 = 2/13/2010
c16 = 1/19/2010 d16 = 2/13/2010

because 1/19/2010 is greater than 01/15/2010, but less than 01/31/2010.

(Columns A through F are all populated with dates.)

I have tried many formulas, using combinations of Vlookup, lookup, and match
(even inverting the lookup array table to accomodate match using -1. This
caused issues with other formulas).

Hi,

The easiest would be to sort column C in descending order and then use this

=INDEX($d$6:$d$11,MATCH(C15,$c$6:$c$11,-1),1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"PAR" wrote in message
...
want to lookup up the date value entered in cells C15 through C22

Lookup table = $C $6 through $D $11 and return value in column 2

Problem - dates in C15 through C22 are date ranges. Formula must return
the
date in column D of the lookup table_array which corresponds to the date
in
cells $C$6 through $C$11 that is =C15 and <= C 15

So if table looks like this

c6 = 1/1/2010 d6 = 1/12/2010
c7 = 1/15/2010 d7 = 1/29/2010
c8 = 1/31/2010 d8 = 2/13/2010
c9 = 2/15/2010 d9 = 2/26/2010
c10 = 2/28/2010 d10 = 3/13/2010
c11 = 3/15/2010 d11 = 3/29/2010

and date is entered in c ; formula in d returns
c15 = 1/31/2010 d15 = 2/13/2010
c16 = 1/19/2010 d16 = 2/13/2010

because 1/19/2010 is greater than 01/15/2010, but less than 01/31/2010.

(Columns A through F are all populated with dates.)

I have tried many formulas, using combinations of Vlookup, lookup, and
match
(even inverting the lookup array table to accomodate match using -1. This
caused issues with other formulas).

Hi,

If you do not want to sort in descending order as suggested earlier, then
try this

=INDEX($D$6:$D$11,IF(ISERROR(MATCH(C15,$C$6:$C$11, 0)),MATCH(TRUE,INDEX((C15-$C$6:$C$11)<0,,1),0),MATCH(C15,$C$6:$C$11,0)),1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"PAR" wrote in message
...
want to lookup up the date value entered in cells C15 through C22

Lookup table = $C $6 through $D $11 and return value in column 2

Problem - dates in C15 through C22 are date ranges. Formula must return
the
date in column D of the lookup table_array which corresponds to the date
in
cells $C$6 through $C$11 that is =C15 and <= C 15

So if table looks like this

c6 = 1/1/2010 d6 = 1/12/2010
c7 = 1/15/2010 d7 = 1/29/2010
c8 = 1/31/2010 d8 = 2/13/2010
c9 = 2/15/2010 d9 = 2/26/2010
c10 = 2/28/2010 d10 = 3/13/2010
c11 = 3/15/2010 d11 = 3/29/2010

and date is entered in c ; formula in d returns
c15 = 1/31/2010 d15 = 2/13/2010
c16 = 1/19/2010 d16 = 2/13/2010

because 1/19/2010 is greater than 01/15/2010, but less than 01/31/2010.

(Columns A through F are all populated with dates.)

I have tried many formulas, using combinations of Vlookup, lookup, and
match
(even inverting the lookup array table to accomodate match using -1. This
caused issues with other formulas).

Can you please explain how this formula works?

"Ashish Mathur" wrote:

Hi,

If you do not want to sort in descending order as suggested earlier, then
try this

=INDEX($D$6:$D$11,IF(ISERROR(MATCH(C15,$C$6:$C$11, 0)),MATCH(TRUE,INDEX((C15-$C$6:$C$11)<0,,1),0),MATCH(C15,$C$6:$C$11,0)),1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"PAR" wrote in message
...
want to lookup up the date value entered in cells C15 through C22

Lookup table = $C $6 through $D $11 and return value in column 2

Problem - dates in C15 through C22 are date ranges. Formula must return
the
date in column D of the lookup table_array which corresponds to the date
in
cells $C$6 through $C$11 that is =C15 and <= C 15

So if table looks like this

c6 = 1/1/2010 d6 = 1/12/2010
c7 = 1/15/2010 d7 = 1/29/2010
c8 = 1/31/2010 d8 = 2/13/2010
c9 = 2/15/2010 d9 = 2/26/2010
c10 = 2/28/2010 d10 = 3/13/2010
c11 = 3/15/2010 d11 = 3/29/2010

and date is entered in c ; formula in d returns
c15 = 1/31/2010 d15 = 2/13/2010
c16 = 1/19/2010 d16 = 2/13/2010

because 1/19/2010 is greater than 01/15/2010, but less than 01/31/2010.

(Columns A through F are all populated with dates.)

I have tried many formulas, using combinations of Vlookup, lookup, and
match
(even inverting the lookup array table to accomodate match using -1. This
caused issues with other formulas).

As long as the lookup date will not be before the min date in the lookup
table or after the max date in the lookup table...

=INDEX(D$6:D$11,MATCH(C15,C$6:C$11)+(COUNTIF(C$6:C $11,C15)=0))

--
Biff
Microsoft Excel MVP


"PAR" wrote in message
...
want to lookup up the date value entered in cells C15 through C22

Lookup table = $C $6 through $D $11 and return value in column 2

Problem - dates in C15 through C22 are date ranges. Formula must return
the
date in column D of the lookup table_array which corresponds to the date
in
cells $C$6 through $C$11 that is =C15 and <= C 15

So if table looks like this

c6 = 1/1/2010 d6 = 1/12/2010
c7 = 1/15/2010 d7 = 1/29/2010
c8 = 1/31/2010 d8 = 2/13/2010
c9 = 2/15/2010 d9 = 2/26/2010
c10 = 2/28/2010 d10 = 3/13/2010
c11 = 3/15/2010 d11 = 3/29/2010

and date is entered in c ; formula in d returns
c15 = 1/31/2010 d15 = 2/13/2010
c16 = 1/19/2010 d16 = 2/13/2010

because 1/19/2010 is greater than 01/15/2010, but less than 01/31/2010.

(Columns A through F are all populated with dates.)

I have tried many formulas, using combinations of Vlookup, lookup, and
match
(even inverting the lookup array table to accomodate match using -1. This
caused issues with other formulas).