date lookup on two column date range

[dim1ann]

hi
i'm having problem with formula for date lookup on range between two
dates which are in two columns.

for example: Lookup Date in A1 11/23/05

lookup table
B1(begin.date) C1(end date) D1(return value)
10/20/05 11/21/05 Nov
11/22/05 12/17/05 Dec
11/23/05 1/30/06 Jan

i need to create a Excel formula (i dont want that function being in
VBA) which will return "Jan" from lookup table

would appreciate a help
cheers
Dim

 

[Jake Marx]

Hi Dim,

I'm not sure what you're looking to do here. 11/23/2005 fits into 2 ranges
in your table - how do you know which one you want?

If 11/23/05 (in B3) is a typo and really should be 12/18/05, then you could
use a formula like this:

=VLOOKUP(A1,$B$1:$D$3,3)

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[dim1ann]

my mistake, i made the typo : date ranges should NOT overlap each other

A1 11/23/05
B1(begin.date) C1(end date) D1(return value)
10/20/05 11/21/05 Nov
11/22/05 12/17/05 Dec
12/23/05 1/30/06 Jan

so a formula should return "Dec" as A1 value within second row of
lookup table

 

[Jake Marx]

Hi dim,

my mistake, i made the typo : date ranges should NOT overlap each
other

A1 11/23/05
B1(begin.date) C1(end date) D1(return value)
10/20/05 11/21/05 Nov
11/22/05 12/17/05 Dec
12/23/05 1/30/06 Jan

so a formula should return "Dec" as A1 value within second row of
lookup table

The formula I provided in my first response should do the trick, then. Did
you try it?

=VLOOKUP(A1,$B$1:$D$3,3)

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]