IF AND formula, returns "Yes" if column matches a cell reference and 2nd column starts with first 3 characters in a reference list

[willow1985]

I am needing help with a IF AND formula.
I am looking to return a "Yes" if Column A Matches G2 and if anything in Column B starts with the first 3 characters from a reference list in Column F.
See below example with desired result in column C.

Thank you to anyone who can help with this.

Book1
	A	B	C	D	E	F	G
1	Company	Reference #	If is Blue Company and Reference starts with List Ref.			Reference	Company
2	Blue Company	CAE51126	Yes			CAE	Blue Company
3	Red Company	DTY8852	No			CAC
4	Blue Company	RRW9543	No			CAG
5	Blue Company	CAT1137	Yes			CAT
6	Red Company	JEQ8842	No
7	Blue Company	CAC84255	Yes
8	Blue Company	CAG745	Yes
9	Red Company	CAC7562	No
Sheet1

 

[hagia_sofia]

How about:

=IF(AND(A2=$G$2,SUM(--(LEFT(B2,3)=$F$2:$F$5))),"Yes","No")

 

[Fluff]

Another option

=IF(AND(A2=$G$2,ISNUMBER(XMATCH(LEFT(B2,3),$F$2:$F$10))),"Yes","No")

 

[willow1985]

How about:

=IF(AND(A2=$G$2,SUM(--(LEFT(B2,3)=$F$2:$F$5))),"Yes","No")

If I had to expand the list in column F to include items with only 2 characters, how would I modify this formula? (see below example - want C8 to be Yes)

Book1
	A	B	C	D	E	F	G
1	Company	Reference #	If is Blue Company and Reference starts with List Ref.			Reference	Company
2	Blue Company	CAE51126	Yes			CAE	Blue Company
3	Red Company	DTY8852	No			CAC
4	Blue Company	RRW9543	No			CAG
5	Blue Company	CAT1137	Yes			CAT
6	Red Company	JEQ8842	No			AE
7	Blue Company	CAC84255	Yes			AC
8	Blue Company	AT5745	No			AT
9	Red Company	CAC7562	No			AG
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=IF(AND(A2=$G$2,SUM(--(LEFT(B2,3)=$F$2:$F$9))),"Yes","No")

 

[Cubist]

You can try:

=IF(SUM((A2=$G$2)*COUNTIFS(B2,$F$2:$F$9&"*")),"Yes","No")

Or (edit):

=IF(SUM(COUNTIFS(B2,$F$2:$F$9&"*",A2,$G$2)),"Yes","No")

 

[willow1985]

You can try:

=IF(SUM((A2=$G$2)*COUNTIFS(B2,$F$2:$F$9&"*")),"Yes","No")

Or (edit):

=IF(SUM(COUNTIFS(B2,$F$2:$F$9&"*",A2,$G$2)),"Yes","No")

Thank you! This is perfect!

 

[Cubist]

Thank you! This is perfect!

You're welcome. Glad we can help.