Combine Spilled Array Columns

[legalhustler]

I have a spilled array formula in cell C1 that returns the results below, but I want to take this result and combine both columns so I can use it in another function as a lookup value to another table. I can do this in a separate column but I would like a single cell formula. How do I amend my spilled array formula so it combines both columns? Note I don't have LAMBDA or LET in my Office 365 yet.

Column C	Column D
01	10
01	11
01	12

I didn't want to confuse anyone but this is my spilled array formula in cell C1:

=UNIQUE(CHOOSE({1,2},RIGHT(FILTER(A1:B4395,XMATCH(RIGHT(A1:A4395,2),UNIQUE(RIGHT(A8:A4395,2)))*XMATCH(LEFT(B1:B4395,2),SORT(UNIQUE(LEFT(B1:B4395,2))))),2),LEFT(FILTER(A1:B4395,XMATCH(RIGHT(A1:A4395,2),UNIQUE(RIGHT(A1:A4395,2)))*XMATCH(LEFT(B1:B4395,2),SORT(UNIQUE(LEFT(B1:B4395,2))))),2)))

Desired spilled array result after combining both columns:

A1

0110

0111

0112

 

[Rick Rothstein]

Assuming your output is not going to Column A like your example indicates you want (formula in C1 is dependent on cell A1:A4395), try this formula in Row 1 of whatever column you actually use, and it should spill correctly...

=C1:C4395&D1:D4395

 

[Fluff]

Maybe

=UNIQUE(INDEX(RIGHT(FILTER(A1:B4395,XMATCH(RIGHT(A1:A4395,2),UNIQUE(RIGHT(A1:A4395,2)))*XMATCH(LEFT(B1:B4395,2),SORT(UNIQUE(LEFT(B1:B4395,2))))),2),,1)&INDEX(LEFT(FILTER(A1:B4395,XMATCH(RIGHT(A1:A4395,2),UNIQUE(RIGHT(A1:A4395,2)))*XMATCH(LEFT(B1:B4395,2),SORT(UNIQUE(LEFT(B1:B4395,2))))),2),,2))

 

[legalhustler]

Maybe

=UNIQUE(INDEX(RIGHT(FILTER(A1:B4395,XMATCH(RIGHT(A1:A4395,2),UNIQUE(RIGHT(A1:A4395,2)))*XMATCH(LEFT(B1:B4395,2),SORT(UNIQUE(LEFT(B1:B4395,2))))),2),,1)&INDEX(LEFT(FILTER(A1:B4395,XMATCH(RIGHT(A1:A4395,2),UNIQUE(RIGHT(A1:A4395,2)))*XMATCH(LEFT(B1:B4395,2),SORT(UNIQUE(LEFT(B1:B4395,2))))),2),,2))

Perfect! Exactly what I was looking for. My original post shows the desired results with the header column A1, it should have said C1 since that's where the formula is and A1 and B1 is where the data is. Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.