Index & Match with Partial string possible?

[NGB82KS]

I have an excel spreadsheet that looks like the below. I need the formula on TAB 1 Column S to calculate if the Class in TAB 1 Column M matches the Master list in TAB 2 Column C, and if that matches, then it looks to see if TAB 1 Column N matches the first 3 characters and if it does return a "Yes" result. (IF TAB 1 Column M matches TAB 2 Column C, & TAB 1 Column N first 3 characters matches TAB 2 Column D first 3 characters)

TAB 1

Class (Column M)	Course ID (Column N)	FORMULA (Column S)
MATH	100 A	YES
MATH	101 C	YES
SCIENCE	200 A	NO

TAB 2

Master Class (Column C)	Master Course ID (Column D)
MATH	100 A
MATH	100 B
SCIENCE	100 1A

 

[Fluff]

That is not the formula that Alan suggested in post#3

 

[NGB82KS]

try this

Book1
	A	B	C	D	E	F
1	Math	100 A	Yes		Math	100 A
2	Math	101 C	No		Math	100 B
3	Science	200 A	No		Science	100 1A
Sheet2

Cell Formulas
Range	Formula
C1:C3	C1	=IF(ISNUMBER(MATCH(A1&LEFT(B1,3),$E$1:$E$3&LEFT($F$1:$F$3,3),0)),"Yes","No")

Sorry that code was for yours, his code was

=IF(ISNUMBER(MATCH(M2&LEFT(N2,3),Table3[CLASS]&LEFT(Table3[COURSE],3),0)),"Yes","No")

and that gives me all NOs, even if i swap the table and put in the $ reference its still all No

 

[Fluff]

It works for me

+Fluff 1.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Class	Course									Class	Course ID	FORMULA
2	MATH	100 A									MATH	100 A	Yes
3	MATH	100 B									MATH	101 C	No
4	SCIENCE	100 1A									SCIENCE	200 A	No
5	HISTORY	200									History	200 A	Yes
6	HISTORY	201
Input

Cell Formulas
Range	Formula
O2:O5	O2	=IF(ISNUMBER(MATCH(M2&LEFT(N2,3),Table3[Class]&LEFT(Table3[Course],3),0)),"Yes","No")

Try confirming it with Ctrl Shift Enter.

 

[NGB82KS]

It works for me

+Fluff 1.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Class	Course									Class	Course ID	FORMULA
2	MATH	100 A									MATH	100 A	Yes
3	MATH	100 B									MATH	101 C	No
4	SCIENCE	100 1A									SCIENCE	200 A	No
5	HISTORY	200									History	200 A	Yes
6	HISTORY	201
Input

Cell Formulas
Range	Formula
O2:O5	O2	=IF(ISNUMBER(MATCH(M2&LEFT(N2,3),Table3[Class]&LEFT(Table3[Course],3),0)),"Yes","No")

Try confirming it with Ctrl Shift Enter.

OK, WELL CONTROL SHIFT ENTER CHANGED EVERYTHING FROM NO WHAT IT APPEARS ITS SUPPOSED TO BE.... DARK EXCEL MAGIC WIZARDY I TELL YOU

 

[Fluff]

Glad we could help & thanks for the feedback.