Returning a value based on two conditions - where there is no winner

[Djmason2001]

Hi Forum,

Sorry to post so much today. I posted a question earlier today and a user helped me find a solution to scenario 1 i outlined below.

However after further analysis on my data i see that i have missed a scenario which i didn't include in my initial thread, consequently the formula doesn't accommodate for the scenario/

Scenario 1) Based on two conditions “Group id(Column B)” and where “Helper (Column C) = "Master” I would like to return a value id(Column A) in Column D. - Resolved
Scenario 2) Based on two conditions “Group id(Column B)” and where “Helper (Column C) = "Joint Master” I would like to return either value id(Column A) in Column D.

Group 21 & 25 - Resolved has part of scenario 1

Formula in the outcome column is the following
=INDEX($A:$A,MATCH(B2&"master",$B$1:$B$100&$C$1:$C$100,0))

ID : Unique Id
Group Id : is a grouping mechanism to identify that two records should be considered for comparison
Helper : Is a indicator column to determine which id should be returned from Column A based on the GroupId.

ID	Group ID	Helper	Outcome
1234	21	Master	1234
2222	21	Non Master	1234
3333	25	Non Master	4444
4444	25	Master	4444
5555	26	Joint Master	5555 or 6666 (No preference on which ID to be returned)
6666	26	Joint Master	5555 or 6666 (No preference on which ID to be returned)

Looking forward to your response

 

[Eric W]

Maybe:

Book1
	A	B	C	D
1	ID	Group ID	Helper	Outcome
2	1234	21	Master	1234
3	2222	21	Non Master	1234
4	3333	25	Non Master	4444
5	4444	25	Master	4444
6	5555	26	Joint Master	5555
7	6666	26	Joint Master	5555
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$100)/($B$2:$B$100=B2)/ISERROR(SEARCH("non",$C$2:$C$100)),1))

 

[Djmason2001]

HI Eric,
Thank you for providing a viable solution.
Is there a way where i can mark this answer as solved ?

 

[Eric W]

Nope, this forum's policy is that threads are not closed or marked "Solved". The reason being that someone might come along later with a different, possibly better version. On some threads there has been some fun synergy where several people throw out ideas and we end up with something that no one person would have come up with. Or possibly someone has a followup question. So just leave a comment saying that you have an acceptable solution so that people don't continue to work on it.

Glad I could help!