Returning Values in one array based on data from two separate arrays

[TenorHobo]

Hi guys first post here.

So I'm trying to pair people from my organization based on their preferences, and such that if:
Person A preferences Person B, and vice versa, the value 1A is returned.
and if Person A preferences Person C SECOND, and Person C preferences Person A FIRST, the value 2A is returned.

I feel like I'm definitely making things overly complicated for myself, but any help would be appreciated!

Thanks!

First Chart
	A	B	C	D
1	Biddee	1st Pref	2nd Pref	3rd Pref
2	David Aikman	Noah BR.	Ian G.	Soph W.
3	Carter Watson	Ethan P.	William R.	Ethan S.
4	Emma Richardson	Rachael B.	Dustin E.	Daphne W.
5	Cadence Harrelson	Avery A.	Ema P.	Yesenia M.
MC

Second Chart
	A	B	C	D
1	Active	1st Pref	2nd Pref	3rd Pref
2	Avery A.	Cadence Harrelson	Emma Richardson	David Aikman
3	Daphne W.	David Aikman	Carter Watson	Emma Richardson
4	Dustin E.	Carter Watson	David Aikman	Cadence Harrelson
5	Ema P.	David Aikman	Emma Richardson	Carter Watson
6	Ethan P.	Carter Watson	Cadence Harrelson	Carter Watson
7	Ethan S.	Cadence Harrelson	Emma Richardson	David Aikman
8	Ian G.	Emma Richardson	David Aikman	Carter Watson
9	Noah BR.	Carter Watson	Emma Richardson	Cadence Harrelson
10	Rachael B.	Carter Watson	David Aikman	Emma Richardson
11	Soph W.	David Aikman	Cadence Harrelson	Carter Watson
12	William R.	Emma Richardson	David Aikman	Carter Watson
13	Yesenia M.	Cadence Harrelson	Carter Watson	David Aikman
Active

Result Chart
	E	F	G	H	I	J	K	L	M	N	O	P	Q
14		Avery A.	Daphne W.	Dustin E.	Ema P.	Ethan P.	Ethan S.	Ian G.	Noah BR.	Rachael B.	Soph W.	William R.	Yesenia M.
15	David Aikman	C	A	B	A	C	C	2B	1	B	3A	B	C
16	Carter Watson		B	A	C	1A	3	C	A	A	C	2C	B
17	Emma Richardson	B	3C	2	B		B	A	B	1C		A
18	Cadence Harrelson	1A		C	2	B	A		C		B		3A
Active

 

[DanteAmor]

Hi and welcome to MrExcel!


The "MC" sheet contains the data as you have in your example.
On the "Active" sheet:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Active	1st Pref	2nd Pref	3rd Pref
2	Avery A.	Cadence Harrelson	Emma Richardson	David Aikman
3	Daphne W.	David Aikman	Carter Watson	Emma Richardson
4	Dustin E.	Carter Watson	David Aikman	Cadence Harrelson
5	Ema P.	David Aikman	Emma Richardson	Carter Watson
6	Ethan P.	Carter Watson	Cadence Harrelson	Carter Watson
7	Ethan S.	Cadence Harrelson	Emma Richardson	David Aikman
8	Ian G.	Emma Richardson	David Aikman	Carter Watson
9	Noah BR.	Carter Watson	Emma Richardson	Cadence Harrelson
10	Rachael B.	Carter Watson	David Aikman	Emma Richardson
11	Soph W.	David Aikman	Cadence Harrelson	Carter Watson
12	William R.	Emma Richardson	David Aikman	Carter Watson
13	Yesenia M.	Cadence Harrelson	Carter Watson	David Aikman
14
15		Avery A.	Daphne W.	Dustin E.	Ema P.	Ethan P.	Ethan S.	Ian G.	Noah BR.	Rachael B.	Soph W.	William R.	Yesenia M.
16	David Aikman	C	A	B	A		C	2B	1	B	3A	B	C
17	Carter Watson		B	A	C	1A	3	C	A	A	C	2C	B
18	Emma Richardson	B	3C	2	B		B	A	B	1C		A
19	Cadence Harrelson	1A		C	2	B	A		C		B		3A
Active

Cell Formulas
Range	Formula
B16:M19	B16	=IFERROR(MATCH(B$15,INDEX(MC!$B$2:$D$5,MATCH($A16,MC!$A$2:$A$5,0),0),0),"")& IFERROR(CHOOSE(MATCH($A16,INDEX($B$2:$D$13,MATCH(B$15,$A$2:$A$13,0),0),0),"A","B","C"),"")

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Active	1st Pref	2nd Pref	3rd Pref			Avery A.	Daphne W.	Dustin E.	Ema P.	Ethan P.	Ethan S.	Ian G.	Noah BR.	Rachael B.	Soph W.	William R.	Yesenia M.
2	Avery A.	Cadence Harrelson	Emma Richardson	David Aikman		David Aikman	C	A	B	A		C	2B	1	B	3A	B	C
3	Daphne W.	David Aikman	Carter Watson	Emma Richardson		Carter Watson		B	A	C	1A	3	C	A	A	C	2C	B
4	Dustin E.	Carter Watson	David Aikman	Cadence Harrelson		Emma Richardson	B	3C	2	B		B	A	B	1C		A
5	Ema P.	David Aikman	Emma Richardson	Carter Watson		Cadence Harrelson	1A		C	2	B	A		C		B		3A
6	Ethan P.	Carter Watson	Cadence Harrelson	Carter Watson
7	Ethan S.	Cadence Harrelson	Emma Richardson	David Aikman
8	Ian G.	Emma Richardson	David Aikman	Carter Watson
9	Noah BR.	Carter Watson	Emma Richardson	Cadence Harrelson
10	Rachael B.	Carter Watson	David Aikman	Emma Richardson
11	Soph W.	David Aikman	Cadence Harrelson	Carter Watson
12	William R.	Emma Richardson	David Aikman	Carter Watson
13	Yesenia M.	Cadence Harrelson	Carter Watson	David Aikman
14
15
16
Sheet2

Cell Formulas
Range	Formula
G2:R5	G2	=MAKEARRAY(4,12,LAMBDA(r,c,IFNA(MATCH(INDEX(G1:R1,,c),FILTER(MC!B2:D5,MC!A2:A5=INDEX(F2:F5,r)),0),"")&IFNA(CHAR(MATCH(INDEX(F2:F5,r),FILTER(B2:D20,A2:A20=INDEX(G1:R1,c)),0)+64),"")))
Dynamic array formulas.