If condition is met, return the last instance...

[rayjay01]

I'm trying to create an IF, Then (do this) statement...

I have a sheet (Joblist) that contains 8 columns.

Column "A" contains a list of customer IDs. So, 1 for Joe Blogs, 2 for Jane Blogs, 3 for Mike Blogs, etc...

Column "F" contains specific jobs that have been requested for complete for the corresponding customer ID. For example, "Boiler Breakdown".

Column "H" contains the status of that job, so it will either be, "Resolved", "On-going" or "Ended". Again, corresponding to the specific job in that row.

All over columns contain irrelevant information to this task.

I have another sheet that allows me to search through data in the "CustomerList" by entering a customer name, this shows me a specific customers address etc. This is easily achieved using the "VLookup" function as there is only one instance of each person. However, what I'm trying to do with this new sheet is obtain data that contains multiple instances of the lookup value.

For example, Joe Blogs (Customer ID - 1) may of had 2 jobs over the last 2 weeks, one 'Resolved', one 'On-going'. Then, Mike Blogs may have 3 jobs done over the last week, 2 'Resolved', 1 'On-going'. I need it to search for the 'On-going' job relating to the customer ID (in this case 1) and skip past any 'Resolved' jobs.

I've tried using the following formula, however it just goes straight to last instance of the customer ID, regardless of what the status is.

=IF(LOOKUP(2,1/(JobList!A:A=C3),JobList!H:H)="Resolved",LOOKUP(2,1/(JobList!A:A=C3),JobList!H:H),IF(LOOKUP(2,1/(JobList!A:A=C3),JobList!H:H)="On-going",LOOKUP(2,1/(JobList!A:A=C3),JobList!F:F),"ERROR"))

Is there any way I can achieve this?

Thanks!

 

[Sal Paradise]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>F</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Customer ID</td><td style="font-weight: bold;;">Job</td><td style="font-weight: bold;;">Status</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">Customer ID:</td><td style=";">Charlie</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Delta</td><td style=";">Job N3</td><td style=";">Resolved</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Delta</td><td style=";">Job A3</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">On-Going Tasks:</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Charlie</td><td style=";">Job B6</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Ended Tasks:</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Alpha</td><td style=";">Job J5</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Resolved Tasks:</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Bravo</td><td style=";">Job A3</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Charlie</td><td style=";">Job Q5</td><td style=";">Resolved</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">On-going Tasks:</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Charlie</td><td style=";">Job T1</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";">Job B6</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Alpha</td><td style=";">Job P0</td><td style=";">Resolved</td><td style="text-align: right;;"></td><td style=";">Job T1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Bravo</td><td style=";">Job M4</td><td style=";">Ended</td><td style="text-align: right;;"></td><td style=";">Job O5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Alpha</td><td style=";">Job Y2</td><td style=";">Ended</td><td style="text-align: right;;"></td><td style=";">Job U6</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Bravo</td><td style=";">Job C0</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";">Job U8</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Delta</td><td style=";">Job B0</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";">Job N3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Bravo</td><td style=";">Job M0</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";">Job Q5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Alpha</td><td style=";">Job N1</td><td style=";">Resolved</td><td style="text-align: right;;"></td><td style=";">Job I7</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Charlie</td><td style=";">Job O5</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Delta</td><td style=";">Job J5</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Alpha</td><td style=";">Job A5</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Charlie</td><td style=";">Job U6</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Alpha</td><td style=";">Job G3</td><td style=";">Resolved</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">Bravo</td><td style=";">Job N5</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Bravo</td><td style=";">Job Q0</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">Charlie</td><td style=";">Job U8</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">Bravo</td><td style=";">Job P5</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">Bravo</td><td style=";">Job U9</td><td style=";">Resolved</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">Delta</td><td style=";">Job J7</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">Delta</td><td style=";">Job O2</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">Bravo</td><td style=";">Job L6</td><td style=";">Resolved</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">Delta</td><td style=";">Job H5</td><td style=";">Resolved</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">Bravo</td><td style=";">Job J4</td><td style=";">Ended</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";">Alpha</td><td style=";">Job F0</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">Charlie</td><td style=";">Job Q7</td><td style=";">Resolved</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";">Delta</td><td style=";">Job P1</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";">Charlie</td><td style=";">Job N3</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style=";">Delta</td><td style=";">Job E0</td><td style=";">Resolved</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style=";">Delta</td><td style=";">Job K3</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style=";">Charlie</td><td style=";">Job Q5</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style=";">Delta</td><td style=";">Job M9</td><td style=";">Ended</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">Alpha</td><td style=";">Job I6</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style=";">Charlie</td><td style=";">Job I7</td><td style=";">On-going</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">CustomerID=K1</font>)*(<font color="Red">Status="On-going"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">CustomerID=K1</font>)*(<font color="Red">Status="Ended"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K5</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">CustomerID=K1</font>)*(<font color="Red">Status="Resolved"</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J8</th><td style="text-align:left">{=IF(<font color="Blue">ROW(<font color="Red">A1</font>)<=$K$3,INDEX(<font color="Red">Job,SMALL(<font color="Green">IF(<font color="Purple">CustomerID=$K$1,IF(<font color="Teal">Status="On-going",ROW(<font color="#FF00FF">Status</font>)-1</font>)</font>),ROW(<font color="Purple">A1</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">CustomerID</th><td style="text-align:left">=OFFSET(<font color="Blue">Sheet1!$A$1,1,0,COUNTA(<font color="Red">Sheet1!$A:$A</font>)-1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Job</th><td style="text-align:left">=OFFSET(<font color="Blue">Sheet1!$F$1,1,0,COUNTA(<font color="Red">Sheet1!$F:$F</font>)-1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Status</th><td style="text-align:left">=OFFSET(<font color="Blue">Sheet1!$H$1,1,0,COUNTA(<font color="Red">Sheet1!$H:$H</font>)-1,1</font>)</td></tr></tbody></table></td></tr></table><br />

Copy the formula in J8 down. Be sure to confirm the curly bracket formulas with Ctrl-Shift-Enter.

 

[T. Valko]

I'm trying to create an IF, Then (do this) statement...

I have a sheet (Joblist) that contains 8 columns.

Column "A" contains a list of customer IDs. So, 1 for Joe Blogs, 2 for Jane Blogs, 3 for Mike Blogs, etc...

Column "F" contains specific jobs that have been requested for complete for the corresponding customer ID. For example, "Boiler Breakdown".

Column "H" contains the status of that job, so it will either be, "Resolved", "On-going" or "Ended". Again, corresponding to the specific job in that row.

All over columns contain irrelevant information to this task.

I have another sheet that allows me to search through data in the "CustomerList" by entering a customer name, this shows me a specific customers address etc. This is easily achieved using the "VLookup" function as there is only one instance of each person. However, what I'm trying to do with this new sheet is obtain data that contains multiple instances of the lookup value.

For example, Joe Blogs (Customer ID - 1) may of had 2 jobs over the last 2 weeks, one 'Resolved', one 'On-going'. Then, Mike Blogs may have 3 jobs done over the last week, 2 'Resolved', 1 'On-going'. I need it to search for the 'On-going' job relating to the customer ID (in this case 1) and skip past any 'Resolved' jobs.

I've tried using the following formula, however it just goes straight to last instance of the customer ID, regardless of what the status is.

=IF(LOOKUP(2,1/(JobList!A:A=C3),JobList!H:H)="Resolved",LOOKUP(2,1/(JobList!A:A=C3),JobList!H:H),IF(LOOKUP(2,1/(JobList!A:A=C3),JobList!H:H)="On-going",LOOKUP(2,1/(JobList!A:A=C3),JobList!F:F),"ERROR"))

Is there any way I can achieve this?

Thanks!

See if this helps...

Lookup when there are multiple instances of the lookup value

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3