NEED HELP WITH MULT IFs STATEMENT WITH INDEXING

Hello all Gurus,

I hope everyone is having a great day. I'm hoping someone can help me create a formula that solves this scenario.

I have a table that I want to add a column that captures the value from another source table. If you look at the provided data sample provided, column D's value is based on two criteria, columns B & C. With those two criteria, I want to index the value from the source table. I have included the expected result in red fonts to show what i want to achieve. There are also times when column B or C is missing data. When that error occurs, just leave it cell blank. The layout of the source table can be adjusted if there is a more simplified way of listing them to achieve the same result. I'm open to any modification that you see fit.

Thank you for taking the time out of your day to assist.