vlookup true finds next larger value that is < lookup value - I need >

[dscs]

Normally VLOOKUP works for me but this time if it can't find an exact match I need it to find the next larger value that is > the lookup value. This is because I am trying to find a row that matches criteria that includes a date and need to find a date that is NOT expired (in the future).

I haven't created custom functions before so I wasn't sure how I could try to create a VLOOKUP where TRUE worked as > instead of <.

Any ideas on how I can accomplish this one?

 

[SkipVought]

Hi,

An example would help.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[AnotherHiggins]

Have a look at

http://www.cpearson.com/excel/lookups.htm#ClosestMatch

It explains how to do exactly what you want.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dscs]

I looked at the link and tried to mess with INDEX / MATCH but I'm still having trouble figuring this out. Here is more detail.

Sheet1 (Lic) has Columns:
A FirmNum-LicSt-LicExpDate
B Firm Num
C Firm Name
D License State
E License #
F License Exp Date
Sheet2 (Policies) has Columns:
A Insured Num
B Insured Name
C Insured State
D Policy Effective Date
E License Firm #

I am trying to pull License # and License Expiration Date into Sheet2 based on the following criteria being met:
Sheet1 ColumnB = Sheet2 ColumnE AND
Sheet1 ColumnD = Sheet2 ColumnC AND
Sheet1 ColumnF >= Sheet2 ColumnD.

I was using Sheet1 ColumnA when trying to use VLOOKUP to find the correct Row.