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