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]

It generates the following value:
Job1|Job2|Job3|Job4

Unclear whether this is a string or array? I'd expect it to return an array from your first formula.

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

 

[bksbksbks]

Unclear whether this is a string or array? I'd expect it to return an array from your first formula.

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

That is the string - the job titles separated by "|"

 

[Cubist]

TEXTSPLIT should return an array. Nested inside XLOOKUP should return an array. It would be helpful if you can post some sample.

 

[Peter_SSs]

TEXTSPLIT should return an array.

I agree

Nested inside XLOOKUP should return an array.

I agree

It would be helpful if you can post some sample.

I agree

@bksbksbks
I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. It also allows us to easily copy that data for testing.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[Peter_SSs]

Ah, I have devised some sample data that does behave this way. The issue, as @Cubist has mentioned is that TEXTSPLIT does produce an array. I believe that the issue for you is that it is returning an array of one item & TEXTSPLIT is looking for a simple string to split, not an array containing a simple string.
Try this modification.

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

If that does not solve the problem then please give us some sample data (any sensitive data disguised or removed) to work with.

 

[bksbksbks]

Ah, I have devised some sample data that does behave this way. The issue, as @Cubist has mentioned is that TEXTSPLIT does produce an array. I believe that the issue for you is that it is returning an array of one item & TEXTSPLIT is looking for a simple string to split, not an array containing a simple string.
Try this modification.

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

If that does not solve the problem then please give us some sample data (any sensitive data disguised or removed) to work with.

INDEX solved the problem! Thank you!

I was having issues with XL2BB, but fortunately this did the trick. Hopefully I can figure that out before my next question!

 

[Peter_SSs]

INDEX solved the problem! Thank you!

You are welcome. Thanks for the confirmation.

I was having issues with XL2BB

Most commonly, issues with XL2BB are resolved per my final note above ..

(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[Cubist]

I have devised some sample data that does behave this way

Do you mind sharing the example for educational purpose? I can't replicate this behavior.

 

[Peter_SSs]

Do you mind sharing the example for educational purpose? I can't replicate this behavior.

Sure

1. Put any value in J1 (eg "a")
2. Put that same value ("a") in named range Overview.JobTitle
3. In the corresponding position of named range Overview.OrgLevel put any value (eg "z")
4. In 'Split Assignment List' cell B3 put that same value ("z")
5. In 'Split Assignment List' cell B4 put say "Job5|Job6|Job7"
6. Now try the formulas from post 1