Alternative to long IFS

[jackcolasu]

I have another long IFS, since that's one of the few formulas I know! Each nurse is assigned a letter A, B, or C to indicate who is covering each other. So the B's cover each other, the C's cover each other, and A covers with the Charge Nurse.

Yesterday, you helped me create the Primary RN list below. Now, I need the corresponding Secondary RNs transcribed to this list:

The names will change every day. The formula I have is below, which basically says if the Primary RN column from above matches E5, then pull B5. If it matches E6, then pull E7. If it matches E7, then pull E6, and so on. Is there something simpler? It's only 5 statements, but I still feel like I'm making it more complicated than it needs to be. Appreciate the feedback!

 

[Cubist]

What do the X's mean and why do Linda/Alice get match up with Yola?

 

[jackcolasu]

What do the X's mean and why do Linda/Alice get match up with Yola?

The numbers are patient rooms, but that part was already solved so the "assigned patient beds" section doesn't matter. I need Yola's & Lina/Alice's names to be paired with each other every time they appears in this list:

Same with Donaldo/Morgan and Frank/Courtney. However, the names and the location of the names change day to day. My IFS formula works, but I wasn't sure if that was the simplest way to achieve my goal. The ABC in the "Break" column indicates who is paired with who. A is always paired with the Charge Nurse, the B's are paired, and the C's are paired. So I need the above list to know that every time the name in E6 (Courtney) appears, E7 (Frank) should appear next to it, since they are both marked "B" in the "Break" column. Same with E5/B5 and E8/E9.

My IFS formula may be the best way, but I'm not great with excel so I just wanted to post here to check if there was a better way.

 

[Cubist]

Try:

Book1
	A	B	C	D	E																			X	Y	Z
1
2
3
4				Break	RN																				RN	Secondary RN
5	Charge Nurse	Yola		A	Linda/Alice*																				Linda/Alice*	Yola
6				B	Courtney																				Courtney	Frank
7				B	Frank																				Frank	Courtney
8				C	Morgan																				Morgan	Donaldo
9				C	Donaldo																				Donaldo	Morgan
Sheet4

Cell Formulas
Range	Formula
Z5:Z9	Z5	=LET(b,$D$5:$D$9,r,$E$5:$E$9,XLOOKUP(1,(b=XLOOKUP(Y5,r,b))*(r<>Y5),r,$B$5))