date between dates

[Ashish]

If I have a sheet with just a date and I want to look up
this date on another sheet where there is a beginning
date and an ending date and a certain category for that
range, I want the formula to return this category.

For example:
My date is Jan 28, 2004. I want to determine which
category it belongs to from a lookup table that is
formatted with three columns like...

A B C
1 Start Date Ending Date Category
2 Jan 1,2004 Jan 15, 2004 ABC
3 Jan 16, 2004 Feb 21, 2004 DEF
4 Feb 22, 2004 Mar 12, 2004 GHI
etc.....

The result I'm looking for is for the formula to
return "DEF" because Jan 28, 2004 is between Jan 16 and
Feb 21.

How do I do this?

Ashish

 

[Domenic]

Hi Ashish,

=INDEX(C2:C4,MATCH(1,(E1>A2:A4)*(E1<B2:B4),0))

entered using CONTROL+SHIFT+ENTER

where E1 contains the date of interest

Hope this helps!

 

[Frank Kabel]

Hi
try
=VLOOKUP(X1,A1:C100,3,1)

where X1 stores the lookup date

 

[Guest]

The lookup would work only if X1=the left most column of
a1:c100. My X1 (lookup value) will perhaps never equal
this date. Then what do I do?

 

[Ashish Vaidya]

Thank you Domenic. That was exactly what I need.

 

[Frank Kabel]

Hi
if your column A is sortes ascending this lookup formula should work (I
set the 4th parameter to '1' / TRUE)

 

[Myrna Larson]

Check Help re the 4th argument for VLOOKUP. He used 1. That means the funciton
will return data from last row where the value in A1:A100 is <= X1. This means
that the A1:C100 range must be sorted on column A. OTOH, if the 4th argument
is 0, only an exact match will be found, and the lookup range does not have to
be sorted.