legalhustler
Well-known Member
- Joined
- Jun 5, 2014
- Messages
- 1,168
- Office Version
- 365
- Platform
- Windows
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.
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:
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 |