P
PAR
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).
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).