2 criterias in lookup

  • Thread starter Thread starter Mouimet
  • Start date Start date
M

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
 
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
 
Back
Top