DateRange Lookup no exact match, can't use lesser

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

 

[Ashish Mathur]

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

 

[Ashish Mathur]

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

 

[rcs]

Can you please explain how this formula works?

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

 

[T. Valko]

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$11,MATCH(C15,C$6:C$11)+(COUNTIF(C$6:C$11,C15)=0))