SOLVED

Limit length of spill formulas, without errors, but error with IFERROR :-)

%3CLINGO-SUB%20id%3D%22lingo-sub-3427958%22%20slang%3D%22en-US%22%3ELimit%20length%20of%20spill%20formulas%2C%20without%20errors%2C%20but%20error%20with%20IFERROR%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3427958%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20set%20of%20data%20that%20I%20would%20like%20to%3A%3CBR%20%2F%3E1)%20filter%20for%20particular%20data%20-%20easily%20accomplished%20using%20FILTER.%3C%2FP%3E%3CP%3E2)%20sort%20according%20to%20particular%20data%20-%20easily%20accomplished%20using%20SORT.%3C%2FP%3E%3CP%3E3)%20limit%20the%20returned%20values%20to%20just%20the%20top%2010%20results%20-%20easily%20accomplished%20using%20INDEX%20and%20SEQUENCE%20(see%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Flimiting-the-length-of-results-from-dynamic-spill-arrays%2Fm-p%2F1058869%22%20target%3D%22_blank%22%3Ehere%3C%2FA%3E%2C%20thanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E)%3C%2FP%3E%3CP%3E4)%20prevent%20the%20%23REF%20error%20populating%20the%20list%20when%20fewer%20than%2010%20results%20are%20available%20-%20in%20theory%20easily%20accomplished%20using%26nbsp%3BIFERROR(%20%3CEM%3Estep%203%20results%3C%2FEM%3E%20%2C%20%22%22).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever...%26nbsp%3B%20IFERROR%20seems%20to%20treat%20strings%20longer%20than%20255%20characters%20as%20an%20error%2C%20but%20%3CU%3Eonly%2C%3C%2FU%3E%20from%20what%20I%20can%20tell%2C%20in%20this%20very%20specific%20scenario.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20have%20any%20ideas%20to%20perform%20step%204%20without%20the%20use%20of%20IFERROR%2C%20or%20know%20why%20IFERROR%20is%20doing%20this%3F%26nbsp%3B%20And%20before%20it%20is%20suggested%2C%20yes%2C%20my%20data%20string%20has%20to%20be%20able%20to%20be%20up%20to%20400%20characters%20unfortunately.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3427958%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3428094%22%20slang%3D%22en-US%22%3ERe%3A%20Limit%20length%20of%20spill%20formulas%2C%20without%20errors%2C%20but%20error%20with%20IFERROR%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3428094%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1401385%22%20target%3D%22_blank%22%3E%40AeroSteve%3C%2FA%3E%26nbsp%3BLet's%20say%20the%20array%20you%20want%20to%20pick%20the%20top%2010%20(or%20fewer)%20from%20is%20in%20A1%23%2C%20try%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(A1%23%2CSEQUENCE(MIN(COUNTA(A1%23)%2C10)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3432874%22%20slang%3D%22en-US%22%3ERe%3A%20Limit%20length%20of%20spill%20formulas%2C%20without%20errors%2C%20but%20error%20with%20IFERROR%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3432874%22%20slang%3D%22en-US%22%3EAhh%2C%20that'll%20do%20it.%20Thanks%20Riny%3C%2FLINGO-BODY%3E
New Contributor

I have a set of data that I would like to:
1) filter for particular data - easily accomplished using FILTER.

2) sort according to particular data - easily accomplished using SORT.

3) limit the returned values to just the top 10 results - easily accomplished using INDEX and SEQUENCE (see here, thanks @Peter Bartholomew)

4) prevent the #REF error populating the list when fewer than 10 results are available - in theory easily accomplished using IFERROR( step 3 results , "").

 

However...  IFERROR seems to treat strings longer than 255 characters as an error, but only, from what I can tell, in this very specific scenario.

 

Anyone have any ideas to perform step 4 without the use of IFERROR, or know why IFERROR is doing this?  And before it is suggested, yes, my data string has to be able to be up to 400 characters unfortunately.

2 Replies
best response confirmed by AeroSteve (New Contributor)
Solution

@AeroSteve Let's say the array you want to pick the top 10 (or fewer) from is in A1#, try this:

=INDEX(A1#,SEQUENCE(MIN(COUNTA(A1#),10)))
Ahh, that'll do it. Thanks Riny