OK I have been working on this for some time and can't quite get things right in the various combinations I have tried, so I am asking for advice.
I tried to post the file, but I could not get it small enough.
Here is my Data Table:
Click image for larger version. 

Name:	Mgr Table.jpg 
Views:	16 
Size:	54.9 KB 
ID:	3091

Here is my Table I am trying to fill out.
Click image for larger version. 

Name:	Mgr Table.jpg 
Views:	16 
Size:	54.9 KB 
ID:	3091

Part 1
There is a possibility that a location would have either a GM or TRM at as the lead manager.
I need a formula that says does the following:
When Store# = (Y), I need the the name of the person who is listed as EITHER GM or TRM. (I do not know which it would be GM/TRM)- If neither is there return "OPEN".

I can get the formula below to give me work with either TRM or GM but can't get the "IF" part to work.
=IFERROR(INDEX('Mgmt Table Data for Area 11'!$C$2:$F$3412,(MATCH(1,(B29='Mgmt Table Data for Area 11'!$C$2:$C$3412)*("GM"='Mgmt Table Data for Area 11'!$E$2:$E$3412),0)),4),"OPEN")

Part 2
How do I get it to give the next person on the list who fits the criteria for the other positions? For Example: Provide me the first name on the list who is a RM at specific Store#, now give me the next person in the list, and the next as I could have multiple people at the same level. I have used the SMALL/ROW function in conjunction with others in the past, but can't quite get the combination correct in this case.

I appreciate any help someone can provide.
Thanks.