Excel: find matrix cell that contains in a string

Question

I want to check which cell value of matrix contains in "Product name" and return a value in the same row, but different column (column A).

I using this function, but it's checks only one column and not a matrix:

=INDEX(A$11:A$13, AGGREGATE(15, 6, ROW($1:$3)*SIGN(MATCH("*"&A$11:A$13&"*", B2, 0)), 1))

In simple words, I want to compute car manufacturer by product name.

Answer 1

This formula will work, but it is quite long and making adding to the matrix Will only add to the length:

=INDEX($A$11:$A$13, IFERROR(AGGREGATE(15, 6, ROW($1:$3)*SIGN(MATCH("*"&A$11:A$13&"*", A2, 0)), 1),IFERROR(AGGREGATE(15, 6, ROW($1:$3)*SIGN(MATCH("*"&$B$11:$B$13&"*", A2, 0)), 1),IFERROR(AGGREGATE(15, 6, ROW($1:$3)*SIGN(MATCH("*"&$C$11:$C$13&"*", A2, 0)), 1),IFERROR(AGGREGATE(15, 6, ROW($1:$3)*SIGN(MATCH("*"&$D$11:$D$13&"*", A2, 0)), 1),0)))))

It basically keeps looking column by column till it finds an answer that works. As said if more models are wanted new, iferror formulas will need to be added for every column in the data matrix.

A quicker shorter method would be a UDF:

Function FINDINSTRING(Srch, rng) As String
Dim strArr() As String
Dim findArr()
Dim i&, j&, k&

findArr = rng.Value

strArr = Split(Srch, " ")


For i = LBound(strArr) To UBound(strArr)
    For j = LBound(findArr, 1) To UBound(findArr, 1)
        For k = LBound(findArr, 2) To UBound(findArr, 2)
            If UCase(strArr(i)) = UCase(findArr(j, k)) Then
                FINDINSTRING = findArr(j, 1)
                Exit Function
            End If
        Next k
    Next j
Next i

FINDINSTRING = "#N/A"

End Function

Which when placed in a module attached to the workbook:

From Excel hit Alt-F11. In that window goto Insert ==> Module. Into that module paste the above code.

Here is a better explanation with pictures.

It can be called directly from the worksheet. In B2 put:

=FINDINSTRING(A2,$A$11:$D$13)

Then it does not matter the size of the data matrix.