Values disappear inconsistently when using TEXTSPLIT

[bksbksbks]

The following formula is working perfectly for me:

=XLOOKUP(TEXTSPLIT(XLOOKUP(J1,Overview.JobTitle,Overview.OrgLevel),"|"),'Split Assignment List'!$B3:$ZZ3,'Split Assignment List'!$B4:$ZZ4,"")

It generates the following value:

Job1|Job2|Job3|Job4

When I wrap the code within a TEXTSPLIT, however, instead of splitting the values into rows (or columns, as I have attempted both), the extra values disappear. The following formula:

=TEXTSPLIT(XLOOKUP(TEXTSPLIT(XLOOKUP(J1,Overview.JobTitle,Overview.OrgLevel),"|"),'Split Assignment List'!$B3:$ZZ3,'Split Assignment List'!$B4:$ZZ4,""),,"|")

generates only the value Job1. The thing that makes it especially frustrating, though, is that when I leave the formula without the TEXTSPLIT and reference it in another cell (say the formula is in A1), the result is perfect. This code:

=TEXTSPLIT(A1,,"|")

Generates the desired result:

Job1
Job2
Job3
Job4

Is there a part of the TEXTSPLIT formula that would eat the other values? I have not encountered this any other time while using the formula.

Thanks for your help!

 

[Cubist]

Interesting - it's treating the 1 cell as an array. The behavior would make sense for more than 1 cell like below. Not sure if this is intended or a bug.

Book1
	A	B
1	Job5|Job6	Job7|Job8
2	Job5	Job7
Sheet5

Cell Formulas
Range	Formula
A1:B1	A1	={"Job5|Job6","Job7|Job8"}
A2:B2	A2	=TEXTSPLIT(A1#,"|")
Dynamic array formulas.

 

[Peter_SSs]

it's treating the 1 cell as an array.

Yes, I mentioned that in post 6.

INDEX solved the problem!

Are you sure? I am having second thoughts.

Your original formulas included this structure TEXTSPLIT(XLOOKUP(J1,Overview.JobTitle,Overview.OrgLevel),"|")
The use here of TEXTSPLIT implies that the value returned from Overview.OrgLevel by the XLOOKUP might itself include one or more "|" characters. If it did include any "|" characters then there would be more than one value being looked for in row 3 of 'Split Assignment List' and therefore more than one value being returned from row 4 of that sheet. If that is the case then my INDEX idea would not return all values.

Consider this

bksbksbks.xlsm
	A	B	C	D
3		z	y	x
4		Job5|Job6|Job7	Job2	Job4|Job8
Split Assignment List

In the sheet below you will see that the yellow and blue are the named ranges.
The example I outlined in post 10 results in the (correct) values in J3:J5

However, in column K the INDEX suggestion that I made only returns 2 values (in K3:K4) from the lookup of "x" in the other sheet. It does not include the values from looking up "y" and "z" as well.
I'm thinking that the formulas in J7 and K7 might be what you actually want to return all values from all lookups on the 'Split Assignment List' sheet?
All that of course depends on whether data like my sample is possible?

bksbksbks.xlsm
	I	J	K	L	M	N
1		a	b
2					a	z
3	Using INDEX	Job5	Job4		b	x|y|z
4		Job6	Job8
5		Job7
6
7	Using TEXTJOIN again	Job5	Job4
8		Job6	Job8
9		Job7	Job2
10			Job5
11			Job6
12			Job7
13
Sheet1

Cell Formulas
Range	Formula
J3:J5,K3:K4	J3	=TEXTSPLIT(INDEX(XLOOKUP(TEXTSPLIT(XLOOKUP(J1,Overview.JobTitle,Overview.OrgLevel),"|"),'Split Assignment List'!$B3:$ZZ3,'Split Assignment List'!$B4:$ZZ4,""),1),,"|")
J7:J9,K7:K12	J7	=TEXTSPLIT(TEXTJOIN("|",1,XLOOKUP(TEXTSPLIT(XLOOKUP(J1,Overview.JobTitle,Overview.OrgLevel),"|"),'Split Assignment List'!$B3:$ZZ3,'Split Assignment List'!$B4:$ZZ4,"")),,"|")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Overview.JobTitle	=Sheet1!$M$1:$M$4	J7:K7, J3:K3
Overview.OrgLevel	=Sheet1!$N$1:$N$4	J7:K7, J3:K3