Forum Discussion
Lookup five characters appearing in any order in a wordlist
My spreadsheet has been working well for the past month, but all of a sudden, I am getting an "empty array" error. I suspect a Microsoft update might be the culprit.
The spreadsheet offers to help resolve the error..,
To resolve the error, either change the criterion, or add the if_empty argument to the https://support.office.com/en-us/f1/topic/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759?NS=EXCEL&Version=90&ThemeId=15&IsSasFeedbackEnabled=True. In this case, =FILTER(C3:D5,D3:D5<100,0) would return a 0 if there are no items in the array.
Here is my formula.
=IF(B2="Out","",FILTER(IFERROR(TRANSPOSE(FILTER($A$2:$A$2849,ISNUMBER(SEARCH(T2,$F$2:$F$2849)))),""),(RIGHT(IFERROR(TRANSPOSE(FILTER($A$2:$A$2849,ISNUMBER(SEARCH(T2,$F$2:$F$2849)))),""),1)=H2)+(LEFT(IFERROR(TRANSPOSE(FILTER($A$2:$A$2849,ISNUMBER(SEARCH(T2,$F$2:$F$2849)))),""),1)=G2)))
I don't know where to add additional if_empty arguments... I already have them in my formula. When I try to add another one, I get an error that there are too many arguments and excel rejects the change.
The formula appears in column AB. The #CALC has not previously been a problem. I would recalculate the spreadsheet using Shift F9, then filter out the blanks and #CALCs in column AB and only the solutions would remain.
But now, no solution words appear in the filter box, just blanks and #CALCs, because my formula no longer works. If I filter out blanks and #CALC, I would have nothing left. I tried it anyway and the spreadsheet would not respond.
I also tried going back to a previous version of the spreadsheet (I save them every day) thinking I may have accidentally changed something, but that isn't the case. I am running Windows 11 and Office 365.
I would appreciate any help.
Thanks,
Michael