2 criterias in lookup

[Mouimet]

Hi,
First I have a table with 4 columns to lookup
Here part of it
MONTH WEEK# From To
JUN 1 2010-May-30 2010-Jun-05
JUN 2 2010-Jun-06 2010-Jun-12
JUN 3 2010-Jun-13 2010-Jun-19
JUN 4 2010-Jun-20 2010-Jun-26
JUN 5 2010-Jun-27 2010-Jul-03
JUL 1 2010-Jul-04 2010-Jul-10
JUL 2 2010-Jul-11 2010-Jul-17
JUL 3 2010-Jul-18 2010-Jul-24

In another sheet I have data with date
Example:
code amount # Date Month
AMA $200 3 2010-Jul-01 ???????

I need to find the ???????
FROM
TO
Meaning: From my list 2010-Jul-01 is between 2010-Jun-27 and 2010-Jul-03
?????? should show "Jun"

I tried with different formula and array, however I can't get the right
result.
I tried with array : =IF((F13>=From)*(F13<=to),1,0)*month
and get #Value instead of JUN

Can you help me please.
Thanks

 

[Jacob Skaria]

Try the below array formula. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

With data in Sheet1 and query date in Sheet2 cell D2 try the below formula
in Sheet2 E2


=INDEX(Sheet1!$A$2:$A$10,MATCH(1,(Sheet1!$C$2:$C$10<=D2)*
(Sheet1!$D$2:$D$10>=D2),0))

If this post helps click Yes