Forum Discussion
Lookup five characters appearing in any order in a wordlist
Hi, all, I have a wordlist and want to write a formula that will find every word that contains five alphabet characters in any order. Here is my formula. It indicates what the first letter mu...
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 FILTER function. 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
Mysteriously started working again...