问在excel中应该使用什么公式/关键字来求解？

EN

Function multiLookup(lookup_value, value_array As range, lookup_array, Optional delim = ",", Optional error_val = "Error")
    multiLookup = "<Not Found>"
    On Error GoTo error
        lookup_ct = UBound(lookup_array, 1) - LBound(lookup_array, 1) + 1

        If lookup_ct <> value_array.Count Then GoTo error

        For i = 1 To value_array.Count
            Debug.Print value_array(i), lookup_array(i, 1)
            If lookup_array(i, 1) = lookup_value Then
                If multiLookup <> "" Then
                    If multiLookup = "<Not Found>" Then
                        multiLookup = ""
                    Else
                        multiLookup = multiLookup & delim & " "
                    End If
                End If
                multiLookup = multiLookup & value_array(i)
            End If
        Next i
    On Error GoTo 0
    Exit Function
error:
    multiLookup = error_val
End Function

Name|Fruit|things|element
David|Apple|pencil|wind
Ana|Banana|eraser|water
Calum|watermelon|pen|earth
Noah|kiwi|ruler|fire
Tom|Banana|eraser|water

Banana|eraser|water

=multiLookup(B8&C8&D8,A1:A6,B1:B6&C1:C6&D1:D6)

lookup_value: this is the target value (in this case Bananaeraserwater from B8:D8)
value_array: this is the label array (in this case the name column)
lookup_array: this is the concatenated data array (fruit col & things col & element col)
delim: Default is a comma, this is the mark that separates output values
error_val: Default is "Error" this is the function return value if there is an error (most likely value_array is not the same length as lookup_array)