| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Four criteria MATCH INDEX lookup of date between matching two text
I got stuck after 2 criterias, can anyone help! I need a formula that returns
1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula shall return 1 if the assembly workcell is occupied on the dates in Sheet2!A2:A1000, and 0 if not. For the formula in Sheet2!B2: Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000 Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000. Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000 I have read several of the MATCH INDEX threads on this forum, but I cant get tit to work with 4 criterias combined with the "between dates". Dates are formated as dd.mm.yyyy. Text is formated as "General". Sheet1: (Letters P, X, Y, AI are col. headings) P X Y AI Status code Cell name Start date Finish date Assembly Cell1 01.10.2008 31.10.2008 Sheet2: (Results in B2 etc.) A B C D Running dates Cell1 Cell2 Cell3 30.09.2008 0 0 0 01.10.2008 1 0 0 02.10.2008 1 0 0 -- Thanks John_J |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Four criteria MATCH INDEX lookup of date between matching two text
Please don't talk about "2 criterias" and "4 criterias".
The singular is criterion, the plural is criteria. Hopefully someone else can answer your question. -- David Biddulph "John_J" wrote in message ... I got stuck after 2 criterias, can anyone help! I need a formula that returns 1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula shall return 1 if the assembly workcell is occupied on the dates in Sheet2!A2:A1000, and 0 if not. For the formula in Sheet2!B2: Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000 Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000. Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000 I have read several of the MATCH INDEX threads on this forum, but I cant get tit to work with 4 criterias combined with the "between dates". Dates are formated as dd.mm.yyyy. Text is formated as "General". Sheet1: (Letters P, X, Y, AI are col. headings) P X Y AI Status code Cell name Start date Finish date Assembly Cell1 01.10.2008 31.10.2008 Sheet2: (Results in B2 etc.) A B C D Running dates Cell1 Cell2 Cell3 30.09.2008 0 0 0 01.10.2008 1 0 0 02.10.2008 1 0 0 -- Thanks John_J |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Four criteria MATCH INDEX lookup of date between matching two
Thank you! Sorry - English is my second language.
-- Thanks John_J "David Biddulph" wrote: Please don't talk about "2 criterias" and "4 criterias". The singular is criterion, the plural is criteria. Hopefully someone else can answer your question. -- David Biddulph "John_J" wrote in message ... I got stuck after 2 criterias, can anyone help! I need a formula that returns 1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula shall return 1 if the assembly workcell is occupied on the dates in Sheet2!A2:A1000, and 0 if not. For the formula in Sheet2!B2: Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000 Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000. Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000 I have read several of the MATCH INDEX threads on this forum, but I cant get tit to work with 4 criterias combined with the "between dates". Dates are formated as dd.mm.yyyy. Text is formated as "General". Sheet1: (Letters P, X, Y, AI are col. headings) P X Y AI Status code Cell name Start date Finish date Assembly Cell1 01.10.2008 31.10.2008 Sheet2: (Results in B2 etc.) A B C D Running dates Cell1 Cell2 Cell3 30.09.2008 0 0 0 01.10.2008 1 0 0 02.10.2008 1 0 0 -- Thanks John_J |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Four criteria MATCH INDEX lookup of date between matching two text
Hi John
As far as I can see you are only using 3 criteria on your Results page. You don't seem to be using Assembly. Anyway, I would first of all create some named ranges to make life simpler. InsertNameDefine Name Start Refers to =Sheet1!Y2:Y1000 Name Finish Refers to =Sheet2!AI2:AI1000 Name Cells Refers to =Sheet1!:X2:X1000 Name Assembly Refers to =Sheet1!P2:P1000 In cell B2 of your Results sheet enter =SUMPRODUCT((Start=$A2)*(Finish(<=$A2)*(Cells=B$1 )) Copy across and down as required -- Regards Roger Govier "John_J" wrote in message ... I got stuck after 2 criterias, can anyone help! I need a formula that returns 1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula shall return 1 if the assembly workcell is occupied on the dates in Sheet2!A2:A1000, and 0 if not. For the formula in Sheet2!B2: Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000 Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000. Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000 I have read several of the MATCH INDEX threads on this forum, but I cant get tit to work with 4 criterias combined with the "between dates". Dates are formated as dd.mm.yyyy. Text is formated as "General". Sheet1: (Letters P, X, Y, AI are col. headings) P X Y AI Status code Cell name Start date Finish date Assembly Cell1 01.10.2008 31.10.2008 Sheet2: (Results in B2 etc.) A B C D Running dates Cell1 Cell2 Cell3 30.09.2008 0 0 0 01.10.2008 1 0 0 02.10.2008 1 0 0 -- Thanks John_J |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Four criteria MATCH INDEX lookup of date between matching two text
=SUMPRODUCT((Start=$A2)*(Finish(<=$A2)*(Cells=B$1 ))
Apologies, a surplus parenthesis crept in there. It should read =SUMPRODUCT((Start=$A2)*(Finish<=$A2)*(Cells=B$1) ) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi John As far as I can see you are only using 3 criteria on your Results page. You don't seem to be using Assembly. Anyway, I would first of all create some named ranges to make life simpler. InsertNameDefine Name Start Refers to =Sheet1!Y2:Y1000 Name Finish Refers to =Sheet2!AI2:AI1000 Name Cells Refers to =Sheet1!:X2:X1000 Name Assembly Refers to =Sheet1!P2:P1000 In cell B2 of your Results sheet enter =SUMPRODUCT((Start=$A2)*(Finish(<=$A2)*(Cells=B$1 )) Copy across and down as required -- Regards Roger Govier "John_J" wrote in message ... I got stuck after 2 criterias, can anyone help! I need a formula that returns 1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula shall return 1 if the assembly workcell is occupied on the dates in Sheet2!A2:A1000, and 0 if not. For the formula in Sheet2!B2: Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000 Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000. Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000 I have read several of the MATCH INDEX threads on this forum, but I cant get tit to work with 4 criterias combined with the "between dates". Dates are formated as dd.mm.yyyy. Text is formated as "General". Sheet1: (Letters P, X, Y, AI are col. headings) P X Y AI Status code Cell name Start date Finish date Assembly Cell1 01.10.2008 31.10.2008 Sheet2: (Results in B2 etc.) A B C D Running dates Cell1 Cell2 Cell3 30.09.2008 0 0 0 01.10.2008 1 0 0 02.10.2008 1 0 0 -- Thanks John_J |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Four criteria MATCH INDEX lookup of date between matching two
In which case, congratulations! It's often difficult making oneself
understood in a technical discussion in one's first language, so you're doing very well in your second. -- David Biddulph "John_J" wrote in message ... Thank you! Sorry - English is my second language. -- Thanks John_J "David Biddulph" wrote: Please don't talk about "2 criterias" and "4 criterias". The singular is criterion, the plural is criteria. Hopefully someone else can answer your question. -- David Biddulph "John_J" wrote in message ... I got stuck after 2 criterias, can anyone help! I need a formula that returns 1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula shall return 1 if the assembly workcell is occupied on the dates in Sheet2!A2:A1000, and 0 if not. For the formula in Sheet2!B2: Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000 Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000. Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000 I have read several of the MATCH INDEX threads on this forum, but I cant get tit to work with 4 criterias combined with the "between dates". Dates are formated as dd.mm.yyyy. Text is formated as "General". Sheet1: (Letters P, X, Y, AI are col. headings) P X Y AI Status code Cell name Start date Finish date Assembly Cell1 01.10.2008 31.10.2008 Sheet2: (Results in B2 etc.) A B C D Running dates Cell1 Cell2 Cell3 30.09.2008 0 0 0 01.10.2008 1 0 0 02.10.2008 1 0 0 -- Thanks John_J |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Four criteria MATCH INDEX lookup of date between matching two text
I might get it right eventually<bg
You said you wanted a zero or 1, not the count. I have also put in a check to prevent a false result if there is no date in column A of your results sheet. =IF($A2="","",IF(SUMPRODUCT((Start=$A2)* (Finish<=$A2)*(Cells=B$1))0,1,0) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... =SUMPRODUCT((Start=$A2)*(Finish(<=$A2)*(Cells=B$1 )) Apologies, a surplus parenthesis crept in there. It should read =SUMPRODUCT((Start=$A2)*(Finish<=$A2)*(Cells=B$1) ) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi John As far as I can see you are only using 3 criteria on your Results page. You don't seem to be using Assembly. Anyway, I would first of all create some named ranges to make life simpler. InsertNameDefine Name Start Refers to =Sheet1!Y2:Y1000 Name Finish Refers to =Sheet2!AI2:AI1000 Name Cells Refers to =Sheet1!:X2:X1000 Name Assembly Refers to =Sheet1!P2:P1000 In cell B2 of your Results sheet enter =SUMPRODUCT((Start=$A2)*(Finish(<=$A2)*(Cells=B$1 )) Copy across and down as required -- Regards Roger Govier "John_J" wrote in message ... I got stuck after 2 criterias, can anyone help! I need a formula that returns 1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula shall return 1 if the assembly workcell is occupied on the dates in Sheet2!A2:A1000, and 0 if not. For the formula in Sheet2!B2: Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000 Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000. Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000 I have read several of the MATCH INDEX threads on this forum, but I cant get tit to work with 4 criterias combined with the "between dates". Dates are formated as dd.mm.yyyy. Text is formated as "General". Sheet1: (Letters P, X, Y, AI are col. headings) P X Y AI Status code Cell name Start date Finish date Assembly Cell1 01.10.2008 31.10.2008 Sheet2: (Results in B2 etc.) A B C D Running dates Cell1 Cell2 Cell3 30.09.2008 0 0 0 01.10.2008 1 0 0 02.10.2008 1 0 0 -- Thanks John_J |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Four criteria MATCH INDEX lookup of date between matching two
Roger,
Thank you for your advice. I tested your suggestion but it retuned "0" all the way down and across. I do need the forth criterion: "Assembly". If it read "Test" ore something else I want the formula to return "0". By the way, I didn't think it was possible to include text values in a SUMPRODUCT formula. The cell name is text. So is the status code "Assembly". That's why I was originally looking for a MATCH INDEX formula. I hope I'm wrong here as I would prefer a SUMPRODUCT formula. -- Thanks John_J "Roger Govier" wrote: I might get it right eventually<bg You said you wanted a zero or 1, not the count. I have also put in a check to prevent a false result if there is no date in column A of your results sheet. =IF($A2="","",IF(SUMPRODUCT((Start=$A2)* (Finish<=$A2)*(Cells=B$1))0,1,0) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... =SUMPRODUCT((Start=$A2)*(Finish(<=$A2)*(Cells=B$1 )) Apologies, a surplus parenthesis crept in there. It should read =SUMPRODUCT((Start=$A2)*(Finish<=$A2)*(Cells=B$1) ) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi John As far as I can see you are only using 3 criteria on your Results page. You don't seem to be using Assembly. Anyway, I would first of all create some named ranges to make life simpler. InsertNameDefine Name Start Refers to =Sheet1!Y2:Y1000 Name Finish Refers to =Sheet2!AI2:AI1000 Name Cells Refers to =Sheet1!:X2:X1000 Name Assembly Refers to =Sheet1!P2:P1000 In cell B2 of your Results sheet enter =SUMPRODUCT((Start=$A2)*(Finish(<=$A2)*(Cells=B$1 )) Copy across and down as required -- Regards Roger Govier "John_J" wrote in message ... I got stuck after 2 criterias, can anyone help! I need a formula that returns 1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula shall return 1 if the assembly workcell is occupied on the dates in Sheet2!A2:A1000, and 0 if not. For the formula in Sheet2!B2: Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000 Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000. Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000 I have read several of the MATCH INDEX threads on this forum, but I cant get tit to work with 4 criterias combined with the "between dates". Dates are formated as dd.mm.yyyy. Text is formated as "General". Sheet1: (Letters P, X, Y, AI are col. headings) P X Y AI Status code Cell name Start date Finish date Assembly Cell1 01.10.2008 31.10.2008 Sheet2: (Results in B2 etc.) A B C D Running dates Cell1 Cell2 Cell3 30.09.2008 0 0 0 01.10.2008 1 0 0 02.10.2008 1 0 0 -- Thanks John_J |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Four criteria MATCH INDEX lookup of date between matching two text
G'day John
With regards to your comment responding to Roger By the way, I didn't think it was possible to include text values in a SUMPRODUCT formula. The cell name is text. So is the status code "Assembly". That's why I was originally looking for a MATCH INDEX formula. I hope I'm wrong here as I would prefer a SUMPRODUCT formula. I use a a 4 Criteria SUMPRODUCT: =SUMPRODUCT(--('2008'!$B$5:$B$2004="SYD"),--('2008'!$D$5:$D$2004="ADE"),--('2008'!$E$5:$E$2004="01.JAN"),--('2008'!$N$5:$N$2004="Accident")) Works fine for what I use it for. HTH Mark. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Four criteria MATCH INDEX lookup of date between matching two
Thank you Roger. I made a silly mistake. When I copied from this forum the
formula was entered in two lines in the formula bar. I just had to delete the line break. To get the correct response I added the criterion "Assembly" and reverted the "<" and "". It now looks like this: =IF($A2="";"";IF(SUMPRODUCT((Start<=$A2)*(Finish= $A2)*(Status="Assembly")*(Cell=B$1))0;1;0) Replace ";" with "," for US region PC's etc. -- Thanks John_J "Roger Govier" wrote: Hi John_J It worked fine on the sample I set up for myself. If you want to send me a copy of your workbook, I will be happy to take a look and see if I can resolve the problem. To mail direct roger at technology4u dot co dot uk Change the at and dots to make a valid email address -- Regards Roger Govier "John_J" wrote in message ... Roger, Thank you for your advice. I tested your suggestion but it retuned "0" all the way down and across. I do need the forth criterion: "Assembly". If it read "Test" ore something else I want the formula to return "0". By the way, I didn't think it was possible to include text values in a SUMPRODUCT formula. The cell name is text. So is the status code "Assembly". That's why I was originally looking for a MATCH INDEX formula. I hope I'm wrong here as I would prefer a SUMPRODUCT formula. -- Thanks John_J "Roger Govier" wrote: I might get it right eventually<bg You said you wanted a zero or 1, not the count. I have also put in a check to prevent a false result if there is no date in column A of your results sheet. =IF($A2="","",IF(SUMPRODUCT((Start=$A2)* (Finish<=$A2)*(Cells=B$1))0,1,0) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... =SUMPRODUCT((Start=$A2)*(Finish(<=$A2)*(Cells=B$1 )) Apologies, a surplus parenthesis crept in there. It should read =SUMPRODUCT((Start=$A2)*(Finish<=$A2)*(Cells=B$1) ) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi John As far as I can see you are only using 3 criteria on your Results page. You don't seem to be using Assembly. Anyway, I would first of all create some named ranges to make life simpler. InsertNameDefine Name Start Refers to =Sheet1!Y2:Y1000 Name Finish Refers to =Sheet2!AI2:AI1000 Name Cells Refers to =Sheet1!:X2:X1000 Name Assembly Refers to =Sheet1!P2:P1000 In cell B2 of your Results sheet enter =SUMPRODUCT((Start=$A2)*(Finish(<=$A2)*(Cells=B$1 )) Copy across and down as required -- Regards Roger Govier "John_J" wrote in message ... I got stuck after 2 criterias, can anyone help! I need a formula that returns 1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula shall return 1 if the assembly workcell is occupied on the dates in Sheet2!A2:A1000, and 0 if not. For the formula in Sheet2!B2: Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000 Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000. Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000 I have read several of the MATCH INDEX threads on this forum, but I cant get tit to work with 4 criterias combined with the "between dates". Dates are formated as dd.mm.yyyy. Text is formated as "General". Sheet1: (Letters P, X, Y, AI are col. headings) P X Y AI Status code Cell name Start date Finish date Assembly Cell1 01.10.2008 31.10.2008 Sheet2: (Results in B2 etc.) A B C D Running dates Cell1 Cell2 Cell3 30.09.2008 0 0 0 01.10.2008 1 0 0 02.10.2008 1 0 0 -- Thanks John_J |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| challenge! - match/index/lookup with multiple criteria | Excel Worksheet Functions | |||
| index / lookup / match / text formula | Excel Discussion (Misc queries) | |||
| index / lookup / match / text formula | Excel Discussion (Misc queries) | |||
| Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
| Only text values matching using index/match lookup - data type pro | Excel Worksheet Functions | |||
Linear Mode
