Join two Arrays???

[rcreek09]

Trying to pair EACH element of A1:A4 with EACH element of B1:B4 to create a new data set of all combinations. Desired results in D1:E16.

Seems like a good candidate for LAMBDA, BYROW, and/or maybe MAP but just can't seem to get there! Even tried PERMUT but no luck.
Am I overcomplicating it or is it just not doable??? Prefer to not use VBA.
Would need to allow for different "sizes" between the two arrays as well.
Using Windows 10, Excel 365.
Thanks!

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	dog	5		dog	5
2	cat	3		dog	3
3	bird	2		dog	2
4	car	4		dog	4
5		7		dog	7
6				cat	5
7				cat	3
8				cat	2
9				cat	4
10				cat	7
11				bird	5
12				bird	3
13				bird	2
14				bird	4
15				bird	7
16				car	5
17				car	3
18				car	2
19				car	4
20				car	7
21
Sheet3

Cell Formulas
Range	Formula
D1:E20	D1	=LET(a,COUNTA(A:A),b,COUNTA(B:B),s,SEQUENCE(a*b,,0),CHOOSE({1,2},INDEX(A1:A10,INT(s/b)+1),INDEX(B1:B10,MOD(s,b)+1)))
Dynamic array formulas.

 

[Dave Patton]

I hesitate to post my first thought.
Try Vstack
Convert to value and then sort or custom sort

LAMBDA.xlsm
	A	B	C				G	H	I	J	K
1	dog	5					dog	5		dog	5
2	cat	3					cat	3		dog	7
3	bird	2					bird	2		dog	4
4	car	4					car	4		dog	2
5		7					dog	7		dog	3
6							cat	5		cat	3
7							bird	3		cat	5
8							car	2		cat	7
9							dog	4		cat	4
10							cat	7		cat	2
11							bird	5		car	4
12							car	3		car	2
13							dog	2		car	3
14							cat	4		car	5
15							bird	7		car	7
16							car	5		bird	2
17							dog	3		bird	3
18							cat	2		bird	5
19							bird	4		bird	7
20							car	7		bird	4
21
8c

Cell Formulas
Range	Formula
G1:G20	G1	=VSTACK(r_1,r_1,r_1,r_1,r_1)
H1:H20	H1	=VSTACK(R_2,R_2,R_2,R_2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
r_1	='8c'!$A$1:$A$4	G1
R_2	='8c'!$B$1:$B$5	H1

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E
1	dog	5		dog	5
2	cat	3		dog	3
3	bird	2		dog	2
4	car	4		dog	4
5		7		dog	7
6				cat	5
7				cat	3
8				cat	2
9				cat	4
10				cat	7
11				bird	5
12				bird	3
13				bird	2
14				bird	4
15				bird	7
16				car	5
17				car	3
18				car	2
19				car	4
20				car	7
21
Sheet4

Cell Formulas
Range	Formula
D1:E20	D1	=HSTACK(TOCOL(IF(SEQUENCE(,COUNTA(B1:B100)),FILTER(A1:A100,A1:A100<>""))),TOCOL(IF(SEQUENCE(,COUNTA(A1:A100)),FILTER(B1:B100,B1:B100<>"")),,1))
Dynamic array formulas.

 

[rcreek09]

All good solutions I'm sure; thanks to all.
Fluff's solution is my preference; I tried it and it works perfectly!

Very thankful to have such wisdom available at my fingertips, and for free!
Rock on!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

Another possible option

23 05 09.xlsm
	A	B	C	D	E
1	dog	5		dog	5
2	cat	3		dog	3
3	bird	2		dog	2
4	car	4		dog	4
5		7		dog	7
6				cat	5
7				cat	3
8				cat	2
9				cat	4
10				cat	7
11				bird	5
12				bird	3
13				bird	2
14				bird	4
15				bird	7
16				car	5
17				car	3
18				car	2
19				car	4
20				car	7
Combine

Cell Formulas
Range	Formula
D1:E20	D1	=LET(b,ROWS(B1:B5),r,b*ROWS(A1:A4),HSTACK(INDEX(A1:A4,SEQUENCE(r,,,1/b)),INDEX(B1:B5,MOD(SEQUENCE(r,,0),b)+1)))
Dynamic array formulas.

.. or if your two ranges are both the same size as in your sample it shortens marginally

23 05 09.xlsm
	A	B	C	D	E
1	dog	5		dog	5
2	cat	3		dog	3
3	bird	2		dog	2
4	car	4		dog	4
5				cat	5
6				cat	3
7				cat	2
8				cat	4
9				bird	5
10				bird	3
11				bird	2
12				bird	4
13				car	5
14				car	3
15				car	2
16				car	4
Combine (2)

Cell Formulas
Range	Formula
D1:E16	D1	=LET(d,A1:B4,b,ROWS(d),r,b^2,HSTACK(INDEX(d,SEQUENCE(r,,,1/b),1),INDEX(d,MOD(SEQUENCE(r,,0),b)+1,2)))
Dynamic array formulas.