How to use Excel VLOOKUP for same letters but different word

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

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: Excel formula to take string value from cell and sort its characters in alphabetical order

We can use the following UDF's to return the needed sorted values:

Function sortletterarr(rng As Range)
    If rng.Columns.Count > 1 Then Exit Function
    If rng.Rows.Count <= 1 Then Exit Function
    Dim out()
    out() = rng.Value
    Dim srtArr() As String
    Dim i As Long, j As Long, k As Long
    Dim a As Long
    For a = LBound(out, 1) To UBound(out, 1)
        ReDim srtArr(1 To Len(out(a, 1)))
        srtArr(1) = Mid(out(a, 1), 1, 1)
        For i = 2 To UBound(srtArr)
            For j = 1 To UBound(srtArr)
                If srtArr(j) = "" Then
                    srtArr(j) = Mid(out(a, 1), i, 1)
                    Exit For
                ElseIf IIf(Asc(Mid(out(a, 1), i, 1)) > 96, Asc(Mid(out(a, 1), i, 1)) - 32, Asc(Mid(out(a, 1), i, 1))) <= IIf(Asc(srtArr(j)) > 96, Asc(srtArr(j)) - 32, Asc(srtArr(j))) Then
                    For k = UBound(srtArr) To j + 1 Step -1
                        srtArr(k) = srtArr(k - 1)
                    Next k
                    srtArr(j) = Mid(out(a, 1), i, 1)
                    Exit For
                End If
            Next j
        Next i
        out(a, 1) = Join(srtArr, "")
    Next a
    sortletterarr = out
End Function

And

Function sortletter(rng As Range)
    If rng.Count > 1 Then Exit Function
    Dim srtArr() As String
    Dim i&, j&, k&
    ReDim srtArr(1 To Len(rng))
    srtArr(1) = Mid(rng, 1, 1)
    For i = 2 To UBound(srtArr)
        For j = 1 To UBound(srtArr)
            If srtArr(j) = "" Then
                srtArr(j) = Mid(rng, i, 1)
                Exit For
            ElseIf IIf(Asc(Mid(rng, i, 1)) > 96, Asc(Mid(rng, i, 1)) - 32, Asc(Mid(rng, i, 1))) <= IIf(Asc(srtArr(j)) > 96, Asc(srtArr(j)) - 32, Asc(srtArr(j))) Then
                For k = UBound(srtArr) To j + 1 Step -1
                    srtArr(k) = srtArr(k - 1)
                Next k
                srtArr(j) = Mid(rng, i, 1)
                Exit For
            End If
        Next j
    Next i
    sortletter = Join(srtArr, "")
End Function

Put both in a normal module attached to the worksheet.

then we can use INDEX/MATCH:

=INDEX($B$1:$B$3,MATCH(sortletter(D1),sortletterarr($A$1:$A$3),0))

And it will return what we want.

This is definitely a primitive approach, but it does work if, as your post indicates, all of your references are of a uniform length, and will work (as an array formula) in versions of Excel from 2007 upwards:

=INDEX($B$1:$B$3,MATCH(SUM(CODE(MID(D1,ROW($1:$5),1))),MMULT(CODE(MID($A$1:$A$3,TRANSPOSE(ROW($1:$5)),1)),ROW($1:$5)/ROW($1:$5)),0))

If your references are not all of the same length (5 in your example) then this formula will not work (even as updated in later versions of Excel, it could compute the same numeric value for 2 entirely different character sequences of unequal length). (if you opt for the VBA solution in the first answer then this article will show you where to put it)

Edit 2-May-2022

JvdV's feedback has indicated a potential issue with the foregoing so, below is an alternate approach, albeit using a helper column (C): the formula in column C1 is

=SUMPRODUCT(LARGE(CODE(MID(A1,ROW($1:$5),1)),ROW($1:$5)),ROW($1:$5)*128)

(which should copied down for each cell of your data)

and the formula in F1 is

=INDEX($B$1:$B$5,MATCH(SUMPRODUCT(LARGE(CODE(MID(E1,ROW($1:$5),1)),ROW($1:$5)),ROW($1:$5)*128),$C$1:$C$5,0))