Error if more than 1 occurance

[traggs25]

Ok so I have a large grid. I have an index match formula that is searching for the teacher & period and returning the course. I want to include a "check" in the formula that shows an error if the same teacher is has their name more than once for the same period.


My formula currently in cell S4 is...

=iferror(if(R4="","",index(B:B,match(R4&$S$3,C:C&A:A,0))))

I would like it to return an error because R4 ("Math1") is listed twice next to S3 ("1").


Hope that makes sense.


<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

A		B		C		D
GEN
7th Red	Course	Teacher		7th Red	Course	Teacher		7th Red	Course	Teacher		7th Red	Course	Teacher
1	Math 7 (Red)	Math1		1				1				1
2	Math 7 (Red)	Math1		2				2				2
3				3				3	Math 7 (Red)	Math1		3
4				4				4	Math 7 (Red)	Math1		4
5				5				5				5
6				6				6				6
7				7				7				7
8				8	Math 7 Lab (Red)	Math1		8				8	Math 7 Lab (Red)	Math3
GEN
7th Blue	Course	Teacher		7th Blue	Course	Teacher		7th Blue	Course	Teacher		7th Blue	Course	Teacher
1	Math 7 (Blue)	Math1		1	Math 7 (Blue)	Math1		1				1
2				2	Math 7 (Blue)	Math1		2				2
3				3				3				3	Math 7 (Blue)	Math1
4				4				4				4	Math 7 (Blue)	Math1
5				5				5				5
6				6				6				6
7				7				7				7
8	Math 7 Lab (Blue)	Math1		8				8	Math 7 Lab (Blue)	Math3		8
SPED
7th Gold	Course	Teacher		7th Gold	Course	Teacher		7th Gold	Course	Teacher		7th Gold	Course	Teacher
1				1				1	Math 7 (Gold)	Math1		1
2				2				2	Math 7 (Gold)	Math1		2
3	Math 7 (Gold)	Math1		3				3				3
4	Math 7 (Gold)	Math1		4				4				4
5				5				5				5
6				6				6				6
7				7				7				7
8				8	Math 7 Lab (Gold)	Math3		8				8	Math 7 Lab (Gold)	Math1

<colgroup><col style="width: 69px"><col width="100"><col width="100"><col width="19"><col width="69"><col width="100"><col width="100"><col width="19"><col width="69"><col width="100"><col width="100"><col width="19"><col width="69"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

 

[Special-K99]

Where's R4 and S3 ?

Is your grid too wide for this forum?

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.