Find if two values occur (regardless of order) in a row of range... simple formula right?

[hinsdale1]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]Jones[/TD]
[TD][/TD]
[TD]White[/TD]
[TD]Jones[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD]True[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith[/TD]
[TD]Johnson[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Group1[/TD]
[TD]Group2[/TD]
[TD]Group3[/TD]
[TD]Group4[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to lookup values A1 & B1 (Smith & Jones) to find the row in range D:E where both values occur regardless of order (can be Jones/Smith or Smith/Jones).

...and... if this matching row has a value of True in Table to right (columns F:I ), return the Group name of the column which is True.

Desired result for data above: For Smith & Jones - matches row 2 of range D:E - this row has a value of True in column G and would return the result of "Group2"

I will also need to adapt the formula for three values lookup for matches in a 3 column range.

It seems like would be pretty straightforward but my brain seems to be broken today.. any help is much appreciated!

 

[Mackers]

Hi

Plenty of smart ways to do this in a single cell but I think the easiest and clearest would be with a helper column as follows to return TRUE or FALSE. This is easy to adapt to 3 values, just add another COUNTIF().

=AND(COUNTIF(D2:E2,$A$2),COUNTIF(D2:E2,$B$2))

Then to return Group2 where the group names are in F5:I5 and your blank and TRUE values are in F2:I4:

=INDEX(F5:I5,MATCH(TRUE,INDEX(F2:I4,MATCH(TRUE,C2:C4,0),0),0))

Hope that helps

Mackers

 

[hinsdale1]

Mackers.. it works perfectly.. and you made my day! I really appreciate you taking the time to help out.. you're a scholar and a gentleman.