Dont always want the first result... Continued

[yuenk]

1. I have spreadsheet that contains a list of tickets and within each ticket they're tasks. On my other spreadsheet is my summary. On the summary sheet James has ticket 55 but I only want to see the first task he has pending. When I use Vlookup to find ticket 55 it will return the first result which is complete. I want to show the task that is pending with the corresponding ticket number to show in my summary spreadsheet.

Solved here with Glenns response: http://www.excelforum.com/excel-form...st-result.html


2. Now on the other hand if ALL the tasks in the ticket are complete I want a value of COMPLETE. I need to combine the formula's for 1 & 2


Thank you for any help.

[daffodil11]

=IF(COUNTIFS(A2:A7,E2,C2:C7,"Complete")=COUNTIF(A2:A7,E2),"Complete",INDEX(B2:B7,MATCH(1,(A2:A7=E2)*(C2:C7="Pending"),0)))

[yuenk]

This almost works. The issue I'm having is when there a ticket that doesn't exist. Example in cell E2 there is a value of 80 but yet in my other sheet of tickets there is no ticket with the value of 80. If there is no match I will end up with the value of complete. In return if there isn't a matching ticket # the value should be blank

[daffodil11]

Why would you be looking up tickets that don't exist? You could use an INDEX(..SMALL( lookup to only list the tickets that exist, and avoid the problem.

Using your example from before, E2:

Please Login or Register  to view this content.

Confirmed with Ctrl+Shift+Enter, and then drag down.