DateRange Lookup no exact match, can't use lesser

  • Thread starter Thread starter PAR
  • Start date Start date
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).
 
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
 
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
 
Can you please explain how this formula works?

Ashish Mathur said:
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
Click to expand...
 
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))
 
Back
Top