Dynamic array - 2 way lookup when both lookup value columns are spilled arrays of the same size

[Magoosball]

Hello!

I'm trying to create a 2 way lookup formula that spills when both lookup value columns are themselves spilled arrays. Both spilled array lookup values are the same size array For example:

=XLOOKUP(Q6#,Sheet1!$1:$1,XLOOKUP(R6#,Sheet1!$A:$A,Sheet1!$1:$1048576))

Is this possible or is there a way around it the value error?
Quick edit --- when I change both Q6# and R6# to just Q6 and R6 respectively and manually fill down the formula it works correctly. I'm trying to avoid the manual step of filling it down.

thank you!

 

[Eric W]

Try the U formula. Your formula is the T formula here.

Book1
	A	B	C	D	E	F	G	H	I	J	K						Q	R	S	T	U
1		B	C	D	E	F	G	H	I	J
2	1	B1	C1	D1	E1	F1	G1	H1	I1	J1
3	2	B2	C2	D2	E2	F2	G2	H2	I2	J2
4	3	B3	C3	D3	E3	F3	G3	H3	I3	J3
5	4	B4	C4	D4	E4	F4	G4	H4	I4	J4
6	5	B5	C5	D5	E5	F5	G5	H5	I5	J5							G	7		G7	G7
7	6	B6	C6	D6	E6	F6	G6	H6	I6	J6							C	1		C1	C1
8	7	B7	C7	D7	E7	F7	G7	H7	I7	J7							E	9		E9	E9
9	8	B8	C8	D8	E8	F8	G8	H8	I8	J8							B	3		B3	B3
10	9	B9	C9	D9	E9	F9	G9	H9	I9	J9							I	9		I9	I9
11	10	B10	C10	D10	E10	F10	G10	H10	I10	J10							D	1		D1	D1
12																	H	9		H9	H9
13
Sheet1

Cell Formulas
Range	Formula
Q6:Q12	Q6	=CHAR(RANDARRAY(7,,66,74,1))
R6:R12	R6	=RANDARRAY(7,,1,10,1)
U6:U12	U6	=INDEX(Sheet1!$1:$1048576,MATCH(R6#,Sheet1!$A:$A,0),MATCH(Q6#,Sheet1!$1:$1,0))
T6:T12	T6	=XLOOKUP(Q6,Sheet1!$1:$1,XLOOKUP(R6,Sheet1!$A:$A,Sheet1!$1:$1048576))
Dynamic array formulas.

 

[Magoosball]

Try the U formula. Your formula is the T formula here.

Book1
	A	B	C	D	E	F	G	H	I	J	K						Q	R	S	T	U
1		B	C	D	E	F	G	H	I	J
2	1	B1	C1	D1	E1	F1	G1	H1	I1	J1
3	2	B2	C2	D2	E2	F2	G2	H2	I2	J2
4	3	B3	C3	D3	E3	F3	G3	H3	I3	J3
5	4	B4	C4	D4	E4	F4	G4	H4	I4	J4
6	5	B5	C5	D5	E5	F5	G5	H5	I5	J5							G	7		G7	G7
7	6	B6	C6	D6	E6	F6	G6	H6	I6	J6							C	1		C1	C1
8	7	B7	C7	D7	E7	F7	G7	H7	I7	J7							E	9		E9	E9
9	8	B8	C8	D8	E8	F8	G8	H8	I8	J8							B	3		B3	B3
10	9	B9	C9	D9	E9	F9	G9	H9	I9	J9							I	9		I9	I9
11	10	B10	C10	D10	E10	F10	G10	H10	I10	J10							D	1		D1	D1
12																	H	9		H9	H9
13
Sheet1

Cell Formulas
Range	Formula
Q6:Q12	Q6	=CHAR(RANDARRAY(7,,66,74,1))
R6:R12	R6	=RANDARRAY(7,,1,10,1)
U6:U12	U6	=INDEX(Sheet1!$1:$1048576,MATCH(R6#,Sheet1!$A:$A,0),MATCH(Q6#,Sheet1!$1:$1,0))
T6:T12	T6	=XLOOKUP(Q6,Sheet1!$1:$1,XLOOKUP(R6,Sheet1!$A:$A,Sheet1!$1:$1048576))
Dynamic array formulas.

Interesting this worked perfectly thank you! I should have thought to try Index Match in this situation instead of Xlookup.
Thank you!