GoogleSheets: Schedule Checker

I have a schedule
01.png

_2MI, _2S_M, _1S_P
these are different classes
Below the classes are the subjects

I edit this sheet.

What i want to do it....
I want a formula that scans/searches the whole sheet for 2 conditional/arguments
and then return a value
02.png
for example, I want it scan for _1M_M
then check under it to see if it has 1Textbook
then return the value of the very top cell in that row
if you refer to the schedule (1st picture) you will see that this is Kristie 02
03.png
as you can see in this pic, i want the value "Kristie 02" to be here to show that there is a cell on the schedule where _1M_M is getting taught 1Textbook

the schedule (1st picture) get extremely complicated, i only showed a small preview. but to eliminate having to manually see if every class and subject is accounted for.
i want the schedule checker to see if the class (horizonally listed) and the subject (vertically listed) is there, if it is, the teacher and classroom will show up.

i've been working with array constrain, array formula, and index, match... but that only works for one colume or row at a time. i want to search the whole sheet. anyways, any help would be appreciated.


Here is the workbook in question: 2023 1st Semester Schedule Bundang.xlsx

Administrative Note:

Welcome to the forum.

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have (NOT newest). Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

OK, so this needs to be in the Google Sheets secition, then - I'll move it now.

Please provide a WORKBOOK as requested.

Where is the workbook I asked for?

Nobody is going to recreate your set-up from your screenshots when you have the workbook and can attach it here.

It's there now - I'll have a look.

Where shall I find your manually calculated expected results? There are no notes in the workbook at all. Did you read what I said before?

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Sample WORKBOOK (1) (1).xlsx
I have made a sample. I hope this makes it easier to understand my request. Thank you.

Thank you again. I'm trying to recreate what you did in my sheet, but it isn't working.

I have entered
=IFERROR(INDEX(MWF!$B$3:$AW$3,1/SUMPRODUCT((MWF!$B$8:$AS$32=$M3)*(MWF!$B$7:$AS$31=N$2)*(COLUMN(MWF!$B:$G)-1))^-1),"")

in the SETTING sheet of the workbook. how could i tweak this to rid of the error?
2023 1st Semester Schedule Bundang.xlsx

The way I fixed the above problem is by making sure all the cells have values. I found that empty cells in the schedule cause an error.

However, even after i put in a value for all of them, I get another problem, which is:
Error:
Numeric value is greater than 1.79769E+308 and cannot be displayed properly.

EDIT: I just made sure there are no blank cells and the formula works exactly as I need it. Thank you so much!