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