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

#### rayjay01

##### New Member
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!

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
<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.

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

Replies
8
Views
445
Replies
0
Views
189
Replies
6
Views
307
Replies
3
Views
2K
Replies
0
Views
406

1,207,261
Messages
6,077,359
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back