Shorter way or shortcut to use IF, AND, AND drawing from 3 variables

[tomeegee]

I have a scoresheet spreadsheet I've developed. I have 12 players playing 5 courses and each course you could be playing FRONT or BACK. Each player has a unique number associated with their name, the course, and whether it's front or back. So:

=IF(AND(B3="Player 1 Name",I3="Course Name",B5="BACK NINE"),'Course HDCPs'!G12)

Works like a charm... for one person. But I want to be able to drop in any name in the Player name field, and swap out the course from a list pull down. I've done this before and my formula is really, really long. For just two players it's: =IF(AND(B22="Player 1 Name",I22="Conestoga",B24="BACK NINE"),'Course HDCPs'!$G$31,IF(AND(B22="Player 2 Name",I22="Conestoga",B24="BACK NINE"),'Course HDCPs'!$G$4)) and so on. 

So for 12 players... =IF(AND(B3="Player Name" x 12 x 5 courses x Front and Back. Is there a way to shortcut this? Or does Excel not care how many logical tests there are? Way above my pay grade. Excel for Mac. Vers 16.37 running on macOS Big Sur Vers 11.6

[#2]

@OliverScheurich 

I am hitting wall on referencing =@INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1))) on another worksheet. The name of this worksheet is ParHCPs. So I've tried referencing COLUMN(ParHCPs!B1) with broken results. I've been searching, but the other COLUMN formulas are treated differently, and I don't want to pollute what you've done.

The other issue is once I've attempted to use the INDEX(INDIRECT on another worksheet and trying to reference, my Lists have all gone wonky and look like this. My biggest fear is breaking everything!