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]

How about

=IF(LEFT(N2,3)=IFERROR(LEFT(INDEX('tab2'!D2:D100,MATCH(M2,'tab2'!C2:C100,0)),3),""),"Yes","No")

 

[AlanY]

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")

 

[NGB82KS]

How about

=IF(LEFT(N2,3)=IFERROR(LEFT(INDEX('tab2'!D2:D100,MATCH(M2,'tab2'!C2:C100,0)),3),""),"Yes","No")

First off, Fluff, your excel knowledge amazes me, I'm just going to start with that.
The code you gave me is working for some, but not all, I have to look at my sheet and see if it's something I did or not. i have 1 thats history 217, but its showing as a no, and i looked at the table and its in the critera... so i'll keep fiddling

 

[Fluff]

If you evaluate the formula using the "Evaluate formula" button on the formula tab, you can see where it's failing.
I would suspect that you have leading/trailing spaces somewhere

 

[NGB82KS]

If you evaluate the formula using the "Evaluate formula" button on the formula tab, you can see where it's failing.
I would suspect that you have leading/trailing spaces somewhere

So when i do the evaluation, it starts evaluating fine, but it doesn't show it looking through the whole range, only the first one, which is history 200, its like it didnt keep evaluating the whole range, just the 1st one it found?

 

[Fluff]

Have you tried Alan's formula?

 

[NGB82KS]

Have you tried Alan's formula?

Yes, his returned all no's, not sure if its because mine is on a separate tab or not

 

[Fluff]

Did you change the ranges to suit your layout?
If so what is the formula you used?

 

[NGB82KS]

Did you change the ranges to suit your layout?
If so what is the formula you used?

So, i checked, the code looks at the 1st one and then stops, it isn't looking at the whole range of all "History" and then all "COURSE"s....
e.g.
TAB1 Table1

ROW	CLASS	COURSE ID (Column N)	FORMULA (Column S)	RESULT
104	HISTORY	200	=IF(LEFT(N104,3)=IFERROR(LEFT(INDEX(Table3[COURSE],MATCH(M104,Table3[CLASS],0)),3),""),"Yes","No")	YES
105	HISTORY	201	=IF(LEFT(N105,3)=IFERROR(LEFT(INDEX(Table3[COURSE],MATCH(M105,Table3[CLASS],0)),3),""),"Yes","No")	NO

TAB 2 Table3

CLASS	COURSE
HISTORY	200
HISTORY	201