Lookup five characters appearing in any order in a wordlist

[mhlester]

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 must be (I1) and what letters must be in the word (anywhere from three to five characters). My formula only searches for words in the wordlis that contain the characters appearing in the same order as in the formula. If I search for w-o-n-d-e, it will find wonderful, but it won't find endow.

Column E is a string containing the letters to search for. Columns R - V show each character in the string in a separate cell.

=IF(C1>=9,VLOOKUP(I1&"*"&LEFT(E1,1)&"*"&MID(E1,2,1)&"*"&RIGHT(E1,1)&"*",$A$2:$F$1200,1,0),IF(C1=8,VLOOKUP(I1&"*"&S1&"*"&R1&"*"&U1&"*"&T1&"*",$A$2:$F$1200,1,0),VLOOKUP(I1&"*"&LEFT(E1,1)&"*"&MID(E1,2,1)&"*"&MID(E1,3,1)&"*"&MID(E1,4,1)&"*"&RIGHT(E1,1)&"*",$A$2:$F$1200,1,0)))

Can someone show me a formula that will work?

Also, even if the order of the characters happens to be right, it will only find the first instance of a word that contains all the characters. I would like to have a list of all the words that contain all the characters.

Thanks,

Michael

[#2]

@PeterBartholomew1 

Unfortunately, I don't know how to implement your formula, but on the plus side... I got it to work, finally, with a nested filter and an "or" function. I kept making little errors in the formula, which is why it wasn't working. And then I would try another approach, and another...

=FILTER(IFERROR(TRANSPOSE(FILTER($A$2:$A$3528,ISNUMBER(SEARCH(AA57,$F$2:$F$3528)))),""),(RIGHT(IFERROR(TRANSPOSE(FILTER($A$2:$A$3528,ISNUMBER(SEARCH(AA57,$F$2:$F$3528)))),""),1)=I57)+(LEFT(IFERROR(TRANSPOSE(FILTER($A$2:$A$3528,ISNUMBER(SEARCH(AA57,$F$2:$F$3528)))),""),1)=H57))

[#3]

@mhlester 

The problem does draw one back.  I had a go at using recursion to remove words that have any letter that is missing from the square.  Although, on average, this requires far fewer comparisons, the calculation time only went down from 5sec to 3sec.

The methods I use have, in the main, only become possible with 365 and I have dropped almost all the standard practices of traditional spreadsheet development.  The new and the traditional approaches share a common function library and work on a 2D grid but little else is similar.

[#4]

@PeterBartholomew1 

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

[#5]

Mysteriously started working again...