SUMPRODUCT formula, need to make additions

[Sethomas5]

Hello and thanks in advance!
I'm using Excel 2013 with Windows 7 Enterprise on a PC.

I am trying to use this formula to see if a course fits in a certain core content category. However, cells have more than one course separated by either a + or a /, like this:

BIO 150 + BIO 151/BSL 215/BIO 141

So, for BIO 150 + BIO 151, I only need to know if "BIO 150" fits in a certain core content category. BUT, then I next need to search for the class (or classes) after the "/".

I am currently using a array that almost works, but if I have more than just, say, BIO 100, I get #N/A, which makes sense, but I don't know how to fix it.

=INDEX('Sheet1'!$A$1:$C$1,IF(SUMPRODUCT((C4='Sheet 1'!$A:$C)*1)=0,NA(),SUMPRODUCT((C4='Sheet 1'!$A:$C)*1*{1,2,3})))

Sheet 1 looks kind of like this:

	A	B	C
1	Natural Sciences	Oral Communications	Written Communications
2	BIO 100	ENG 100	COM 100
3	BIO 151	ENG 101	COM 101
4	BIO 152	ENG 102

The course that I am referencing is in another sheet and is in column C (C4 in my example formula).

	A	B	C
1	EKU CLASS	CLASS NAME	KCTCS CLASS
2	BIO 111	Biology	BIO 151 + BIO 152/BSL 215
3	BIO 112	Another Bio	BIO 152 + BIO 153/BIO 141/BSL 140
4	BIO 316	Another Bio	BIO 100

 

[svendiamond]

Not sure exactly what your expected results are... can you post an example of what you want your data to look like?

 

[Sethomas5]

@svendiamond

Yes! I suppose I should have done that earlier. Sorry, it was almost lunch time.
Here is what I want my data to look like:

	A	B	C	D
1	EKU Class	Class Name	KCTCS Class(es)	Core Category
2	BIO 111	Biology	BIO 151 + BIO 152	Natural Sciences
3			BSL 215
4	BIO 112	Another Bio	BIO 152 + BIO 153	Natural Sciences
4			BIO 141
5			BSL 140
6	BIO 316	Another BIO	BIO 100	Natural Sciences
7	ENG 100	English class	ENG 101	Written Communications
8	CMS 100	COM class	COM 100	Oral Communciations

I want to match the class from column C (KCTCS Class(es)) to one in Sheet 1 and return the header for that column (such as Natural Sciences) in column D on my second sheet.
I'm thinking a different, easier formula, altogether would do this for me.

As you can see from the above table, I'd REALLY like for the classes that are separated by a "/" to be on a new line. I know that would be another formula.

 

[Sethomas5]

Okay, I need to clear this up a lot!

 

[svendiamond]

I can probably come up with some code to accomplish your desired results. Are there ever times when there are multiple classes in a single cell in column C?

 

[Sethomas5]

@svendiamond

I've sorted out two of my problems that I couldn't figure out before. So, my starting data has changed a little, but I think it will be much easier.

Here is an example of my starting data, mind you there can be blanks in Column C (example in row 4) and many many rows.

	A	B	C	D
1	EKU COURSE		KCTCS COURSE	CORE CATEGORY
2	ACC 201	NAME OF CLASS	ACC 201
3			ACT 101 + ACT 102
4	ACC 202	NAME OF CLASS
5	QMB 200	NAME OF CLASS	STA 220
6			ST 291

So, as you can see, right now I have each class in column C that could be equivalent to a class in column A in a separate row.
Now, I just need to be able to fit it into my sumproduct formula to only reference the first 7 characters in Column C.

Here is my formula I am using again:

=INDEX('Sheet1'!$A$1:$C$1,IF(SUMPRODUCT((C4='Sheet 1'!$A:$C)*1)=0,NA(),SUMPRODUCT((C4='Sheet 1'!$A:$C)*1*{1,2,3})))

 

[Sethomas5]

If there is an easier, prettier formula, I am open to that.
Or a macro.

 

[svendiamond]

I think your formula works fine, however, referring to entire columns may be slowing it down, as it is looking through three million cells... just change it to:

=INDEX(Sheet1!$A$1:$C$1,IF(SUMPRODUCT((C2=Sheet1!$A$1:$C$100)*1)=0,NA(),SUMPRODUCT((C2=Sheet1!$A$1:$C$100)*1*{1,2,3})))

Also, if you want to refer to just the LEFT 7 characters of column C, the fastest and easiest way would be to add a helper column that gets those characters (i.e. =LEFT(C2,7)) and then refer to that column in your formula. You can always hide the helper column.

 

[Sethomas5]

@svendiamond

Okay, that sounds like it could work. Buuuuut, I'm going to need help creating the helper column and hiding it. Especially the hiding part. I've never done that.

Thanks so much for your help!
And I will definitely edit my formula to make it faster.

 

[Sethomas5]

@svendiamond

Created/used the =LEFT formula fine. I'm sure I can search these threads to figure out how to hide a column, so thank you SO much for your help!