Return a value after searching a list for any of the terms in another list?

[adacause]

Hi,

I have a table of student schedules that I need to search for any of a long list of different classes (Math 1, Math 2, etc.) in a named range (Math). If the search finds any of the courses from the named range in a student’s schedule it would return what period of the day the student has that class. Students' names are in column 1, while the next 6 columns are periods 1, 2, 3… Is there any easy way to search for these (Math 1, Math 2, Math 3, and another 12) in table like the one below and return the last column? What function or formula would I use to get the last column in the table below? I’ve tried just about everything I can think of. Thanks.

	1	2	3	4	5	6	Math Period?
Student A	Math 1	ELA	Spanish 1	PE	World History	Biology	1
Student B	ELA	PE	Spanish 3	US History	Math 3	Chemistry	5
Student C	ELA	TA	Woodshop	Math 2	Biology	Art	4
Student D	Math 1	Spanish 1	PE	Biology	ELA	World History	1

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?

22 03 09.xlsm
	A	B	C	D	E	F	G	H
1		1	2	3	4	5	6	Math Period?
2	Student A	Math 1	ELA	Spanish 1	PE	World History	Biology	1
3	Student B	ELA	PE	Spanish 3	US History	Math 3	Chemistry	5
4	Student C	ELA	TA	Woodshop	Math 2	Biology	Art	4
5	Student D	Math 1	Spanish 1	PE	Biology	ELA	World History	1
Classes

Cell Formulas
Range	Formula
H2:H5	H2	=MATCH("Math*",B2:G2,0)

 

[adacause]

Welcome to the MrExcel board!

Is this what you mean?

22 03 09.xlsm
	A	B	C	D	E	F	G	H
1		1	2	3	4	5	6	Math Period?
2	Student A	Math 1	ELA	Spanish 1	PE	World History	Biology	1
3	Student B	ELA	PE	Spanish 3	US History	Math 3	Chemistry	5
4	Student C	ELA	TA	Woodshop	Math 2	Biology	Art	4
5	Student D	Math 1	Spanish 1	PE	Biology	ELA	World History	1
Classes

Cell Formulas
Range	Formula
H2:H5	H2	=MATCH("Math*",B2:G2,0)

Thanks. Sort of. I can't use the wildcard because not all the course names have the term "math" - like "statistics" or "calculus". I have a named range called "Math" with all the course names in it (I don't know if that makes it easier or harder).

When I try the match function I get an array that spills down into the next row with "#N/A", and then eventually the correct period shows up In the list of "#N/A"s. Sorta like the list below, except longer. Thanks.

#N/A

#N/A

#/NA

6

#N/a

 

[Peter_SSs]

Is it more like this then?

22 03 09.xlsm
	A	B	C	D	E	F	G	H	I	J
1		1	2	3	4	5	6	Language Class		Language
2	Student A	Math 1	ELA	Spanish 1	PE	World History	Biology	3		Spanish 1
3	Student B	ELA	PE	Spanish 3	US History	French 2	Chemistry	3, 5		Spanish 2
4	Student C	ELA	TA	Woodshop	Math 2	Biology	French 2	6		Spanish 3
5	Student D	Math 1	Spanish 1	PE	Biology	ELA	World History	2		French 1
6										French 2
7
Classes

Cell Formulas
Range	Formula
H2:H5	H2	=TEXTJOIN(", ",1,FILTER(B$1:G$1,ISNUMBER(MATCH(B2:G2,Language,0)),""))

Named Ranges
Name	Refers To	Cells
Language	=Classes!$J$2:$J$6	H2:H5

 

[adacause]

Is it more like this then?

22 03 09.xlsm
	A	B	C	D	E	F	G	H	I	J
1		1	2	3	4	5	6	Language Class		Language
2	Student A	Math 1	ELA	Spanish 1	PE	World History	Biology	3		Spanish 1
3	Student B	ELA	PE	Spanish 3	US History	French 2	Chemistry	3, 5		Spanish 2
4	Student C	ELA	TA	Woodshop	Math 2	Biology	French 2	6		Spanish 3
5	Student D	Math 1	Spanish 1	PE	Biology	ELA	World History	2		French 1
6										French 2
7
Classes

Cell Formulas
Range	Formula
H2:H5	H2	=TEXTJOIN(", ",1,FILTER(B$1:G$1,ISNUMBER(MATCH(B2:G2,Language,0)),""))

Named Ranges
Name	Refers To	Cells
Language	=Classes!$J$2:$J$6	H2:H5

You sir are a wonderful human being. I spent hours trying to figure that out. I will have to study that for a bit and figure out what it means. Thank you.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

(For future questions, consider XL2BB for your sample data.)