Vlookup and match

[Access Beginner]

Hi all,

I have the following table.



In Cell B5 I'll have the value of Region 1 and in Cell C4 I'll have the value of T1.3. I'd like to have a formula that will return "Yes" if T1.3 is in the row that contains Region 1.

Row	Column R	Column S	Column T	Column U
44​	Region	Priority One	Priority Two	Priority Three
45​	Region 1	T1.3	T1.5	T2.1
46​	Region 2	T1.3	T2.1	T3.1
47​	Region 3	T1.3	T1.5	T2.1
48​	Region 4	T1.3	T2.1	T3.1
49​	Region 5	T1.3	T2.1	T1.2
50​	Region 6	T1.4	T2.1	T1.1
51​	Region 7	NA	NA	NA
52​	Region 8	T1.3	T1.5	T2.1
53​	Region 9	T1.2	T1.3	T2.1
54​	Region 10	T1.3	T1.4	T2.1
55​	Region 11	T1.2	T1.3	T2.1
56​	Region 12	T1.3	T1.5	T2.1
57​	Region 13	T1.4	T2.1	T2.4
58​	Region 14	T1.3	T1.5	T2.1
59​	Region 15	T1.5	T2.1	T3.1
60​	Region 16	T1.3	T1.5	T2.1
61​	Region 17	T1.3	T1.5	T2.1

In Cell B5 I'll have the value of Region 1 and in Cell C4 I'll have the value of T1.3. I'd like to have a formula that will return "Yes" if T1.3 is in the row that contains Region 1.

I have the following formula "=IFERROR(IF(MATCH(S$66,$S$46:$U$46,0)<>"","Yes",""),"")" . However I don't want to have different formulas for each Region.

Example if the desired outcome is below:

	T1.1	T1.2	T1.3	T1.4	T1.5	T2.1
Region 1			Yes		Yes	Yes
Region 2			Yes			Yes

 

[DanteAmor]

How about:

Book1
	B	C	D	E	F											Q	R	S	T	U
1
4		T1.1	T1.2	T1.3	T1.4
5	Region1			Yes
6	Region2			Yes
7	Region3			Yes
8	Region4			Yes
9	Region5			Yes
10	Region6				Yes
43
44																	Region	PriorityOne	PriorityTwo	PriorityThree
45																	Region1	T1.3	T1.5	T2.1
46																	Region2	T1.3	T2.1	T3.1
47																	Region3	T1.3	T1.5	T2.1
48																	Region4	T1.3	T2.1	T3.1
49																	Region5	T1.3	T2.1	T1.2
50																	Region6	T1.4	T2.1	T1.1
51																	Region7	NA	NA	NA
52																	Region8	T1.3	T1.5	T2.1
Hoja11

Cell Formulas
Range	Formula
C5:F10	C5	=IF(COUNTIFS($R$45:$R$61,$B5,$S$45:$S$61,C$4),"Yes","")

 

[steve the fish]

Another option, again in C5:

=IF(ISNUMBER(MATCH(C$4,INDEX($S$45:$U$61,MATCH($B5,$R$45:$R$61,0),0),0)),"Yes","")

 

[Access Beginner]

Another option, again in C5:

=IF(ISNUMBER(MATCH(C$4,INDEX($S$45:$U$61,MATCH($B5,$R$45:$R$61,0),0),0)),"Yes","")

Thanks works perfectly

 

[DanteAmor]

I thought it was just a column, then how about in C5:

=IF(SUMPRODUCT(($R$45:$R$61=$B5)*(($S$45:$U$61=C$4))),"Yes","")