hi vt78cruiser, welcome to the forum. could you standardize the subject spelling in the 2 sheets? so C3 should also read "English Teacher" instead of just "Eng". then in B2, try this array formula:
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
in E3 & E4, you'll see 0. that's because there is such a student & subject, but "Teacher" field was blank. to hide this, format all your cells to Custom:
General;;
Bookmarks