# Index & Match with Partial string possible?

#### NGB82KS

##### Board Regular
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)
MATH100 AYES
MATH101 CYES
SCIENCE200 ANO

TAB 2
Master Class (Column C)Master Course ID (Column D)
MATH100 A
MATH100 B
SCIENCE100 1A

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=IF(LEFT(N2,3)=IFERROR(LEFT(INDEX('tab2'!D2:D100,MATCH(M2,'tab2'!C2:C100,0)),3),""),"Yes","No")``

#### AlanY

##### Well-known Member
try this

Book1
ABCDEF
1Math100 AYesMath100 A
2Math101 CNoMath100 B
3Science200 ANoScience100 1A
Sheet2
Cell Formulas
RangeFormula
C1:C3C1=IF(ISNUMBER(MATCH(A1&LEFT(B1,3),\$E\$1:\$E\$3&LEFT(\$F\$1:\$F\$3,3),0)),"Yes","No")

#### NGB82KS

##### Board Regular
Excel Formula:
``=IF(LEFT(N2,3)=IFERROR(LEFT(INDEX('tab2'!D2:D100,MATCH(M2,'tab2'!C2:C100,0)),3),""),"Yes","No")``
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

##### MrExcel MVP, Moderator
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

##### Board Regular
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

##### MrExcel MVP, Moderator
Have you tried Alan's formula?

#### NGB82KS

##### Board Regular
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

##### MrExcel MVP, Moderator
Did you change the ranges to suit your layout?
If so what is the formula you used?

#### NGB82KS

##### Board Regular
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
ROWCLASSCOURSE ID (Column N)FORMULA (Column S)RESULT
104HISTORY200
Excel Formula:
``=IF(LEFT(N104,3)=IFERROR(LEFT(INDEX(Table3[COURSE],MATCH(M104,Table3[CLASS],0)),3),""),"Yes","No")``
YES
105HISTORY201
Excel Formula:
``=IF(LEFT(N105,3)=IFERROR(LEFT(INDEX(Table3[COURSE],MATCH(M105,Table3[CLASS],0)),3),""),"Yes","No")``
NO

TAB 2 Table3
CLASSCOURSE
HISTORY200
HISTORY201

Replies
4
Views
194
Replies
5
Views
372
Replies
3
Views
97
Replies
3
Views
104
Replies
3
Views
126

1,191,170
Messages
5,985,063
Members
439,938
Latest member
MAlhash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back