Formula Help

[asad]

Hello Guys,

I have the following table:

[TABLE="width: 291"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Driver[/TD]
[TD]Location[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]G.J.[/TD]
[TD]WS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]G.J.[/TD]
[TD]CC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]G.J.[/TD]
[TD]CC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]G.J.[/TD]
[TD]HE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]G.J.[/TD]
[TD]HE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]G.J.[/TD]
[TD]DD[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]G.J.[/TD]
[TD]DD[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]G.J.[/TD]
[TD]TD[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P.S.[/TD]
[TD]TD[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]G.J.[/TD]
[TD]T4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P.S.[/TD]
[TD]T4[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

In this, I want to get a result in third column by matching values in columns A and B. I also have a named range "AP" that includes values T4, T3, and T1.
Now, I want to check if the value in B2 is WS, and the values in A3 to A12 are matching with A2, and the corresponding values in B3 to B12 are matching with any of the values in range "AP", then True otherwise give me false.
In the above data, the answer should be True, but if the A11 value is changed from G.S. to any other value, then it should be False.

 

[Aladin Akyurek]

Hard to follow. Try to post the input and the expected output along with the conditions which lead to that output.

 

[asad]

Hard to follow. Try to post the input and the expected output along with the conditions which lead to that output.

The input is same as I already posted above. But here it is again with the expected answer in C2.

	A	B	C
1	Driver	Location
2	G.J.	WS	TRUE
3	G.J.	CC
4	G.J.	CC
5	G.J.	HE
6	G.J.	HE
7	G.J.	DD
8	G.J.	DD
9	G.J.	TD
10	P.S.	TD
11	G.J.	T4
12	P.S.	T4

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

The conditions are If B2="WS", and If A3:A12=A2, and if coressponding B3:B12 = named range "AP", then answer should be TRUE. "AP" has values of T4, T3, and T1.
In the case above, if I change G.J. in A11 to anything else other than G.J., then the answer should change to False.

Hope I have explained it a bit better than my first post.

 

[Aladin Akyurek]

What do you mean by this, if coressponding B3:B12 = named range "AP"?

 

[asad]

I tried to say that in the example above, cells A3, A4, A5, A6, A7, A8, A9, and A11 have matching values to A2. So I want formula to look at B3, B4, B5, B6, B7, B8, B9, and B11 to see if any of these cells have any of the values (t1, t3, and T4) then answer should be yes. There may be more than one set of matching values, but then it will not affect the answer. The answer is still True.
But if B11 in the above example is changed to DD or anything else and B12 says as T4, the answer should be False.

 

[asad]

Sorry for making a simple thing so complex by my poor explanation.

 

[Aladin Akyurek]

Let D1 house G.J.

In D2 control+shift+enter, not just enter:

=SUM(ISNUMBER(MATCH(IF($A$2:$A$12=D1,$B$2:$B$12),{"T1","T3","T4"},0))+0)>0

 

[asad]

Thanks a lot Aladin sir.
I will check it tomorrow and get back to you with the result.

 

[asad]

Let D1 house G.J.

In D2 control+shift+enter, not just enter:

=SUM(ISNUMBER(MATCH(IF($A$2:$A$12=D1,$B$2:$B$12),{"T1","T3","T4"},0))+0)>0

Thanks a lot Sir.

The formula works perfect.

 

[Aladin Akyurek]

Thanks a lot Sir.

The formula works perfect.

You are very welcome. Thanks for the update.