Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date lookup on two column date range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
date lookup on two column date range
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] wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
date lookup on two column date range
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
date lookup on two column date range
Hi dim,
wrote: 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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup date range row A and count if leave in same column on row B | Excel Discussion (Misc queries) | |||
Lookup based on a date being between a range | Excel Worksheet Functions | |||
Lookup / return latest date in range AND value of 1 column to the | Excel Worksheet Functions | |||
2 column lookup - match to date range | Excel Worksheet Functions | |||
sum, lookup, and date range | Excel Worksheet Functions |