How to use Excel VLOOKUP for same letters but different word

Question

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.

Answer 1

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)))