How to use Excel VLOOKUP for same letters but different word-CodePudding

I need to find the values of words with the same letters but different order of letters. I tried using vlookup but it didn't work, any suggestions how to do that? For example, the letters are the same in the photo, but their order is different and I cannot find the value.

CodePudding user response：

With Office 365 we can use XLOOKUP with some other dynamic array formula:

=XLOOKUP(CONCAT(SORT(MID(D1,SEQUENCE(,5),1),1,1,TRUE)),BYROW(MID($A$1:$A$3,SEQUENCE(,5),1),LAMBDA(A,CONCAT(SORT(A,1,1,TRUE)))),$B$1:$B$3,"")

This will order the letters in alphabetical order virtually so that they will find the matches.

If one does not have Office 365 this will be easier with vba. Based on an older answer:

CodePudding user response：

Another alternative LAMBDA() related option using REDUCE():

Formula in E1:

=XLOOKUP("",REDUCE(A$1:A$3,MID(D1,SEQUENCE(LEN(D1)),1),LAMBDA(a,b,SUBSTITUTE(a,b,"",1))),B$1:B$3)

Or; spill the entire range at once wrapping the above in BYROW():

Formula in E1:

=BYROW(D1:D3,LAMBDA(a,XLOOKUP("",REDUCE(A$1:A$3,MID(a,SEQUENCE(LEN(a)),1),LAMBDA(b,c,SUBSTITUTE(b,c,"",1))),B$1:B$3)))

The above would assume lookup values that are of the exact same length. To avoid false positives we should probably concatenate our starting value first:

=XLOOKUP(D1,REDUCE(A$1:A$3&D1,MID(D1,SEQUENCE(LEN(D1)),1),LAMBDA(a,b,SUBSTITUTE(a,b,"",1))),B$1:B$3)

And respectively:

=BYROW(D1:D3,LAMBDA(a,XLOOKUP(a,REDUCE(A$1:A$3&a,MID(a,SEQUENCE(LEN(a)),1),LAMBDA(b,c,SUBSTITUTE(b,c,"",1))),B$1:B$3)))