# DateRange Lookup no exact match, can't use lesser

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

A

#### 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

A

#### 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

R

#### rcs

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

T

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