Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 criterias in lookup
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 criterias in lookup
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$1 0<=D2)* (Sheet1!$D$2:$D$10=D2),0)) If this post helps click Yes --------------- Jacob Skaria "Mouimet" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you have 2 criterias be picked up in a lookup? | Excel Worksheet Functions | |||
how do you have 2 criterias be picked up in a lookup? | Excel Discussion (Misc queries) | |||
Too many criterias... | Excel Worksheet Functions | |||
Sum If using 2 criterias | Excel Discussion (Misc queries) | |||
Lookup with 3 criterias | Excel Worksheet Functions |