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

[hinsdale1]

A	B		D	E	F	G	H	I
Smith	Jones		White	Jones				True
			Jones	Smith		True
			Smith	Johnson
					Group1	Group2	Group3	Group4

<tbody>
</tbody>

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.