Search Array for part of a cell (Custom Function)

[C_Rieker]

Hello all.

I have the need to search a range of cells (List of Categories) to match only part of a given cell (Description). When found, the value from the matching cell (List of Categories) is displayed as the output (Category).

I am running into a problem where it only works if there is a match in the first cell of the range. it may be that it is super late but i am struggling to find why the error occurs.

Here is my custom function

Public Function SearchArr(StrToMatch As String, RngToSearch As Range)

    Dim c As Range

    For Each c In RngToSearch
        If IsError(WorksheetFunction.Search(c.Value, StrToMatch)) Then
            Else
                If IsNumeric(WorksheetFunction.Search(c.Value, StrToMatch)) Then
                    SearchArr = c.Value
                    Exit Function
                    
                    Else
                End If
        End If
    Next c

' -------------VBA I Previously tried and also failed ---------------
'    Dim i As Long
'
'    For i = LBound(RngToSearch) To UBound(RngToSearch)
'        If IsNumeric(WorksheetFunction.Search(RngToSearch(i).Value, StrToMatch)) Then
'            SearchArr = RngToSearch(i).Value
'            Exit Function
'            Else
'                If i = UBound(RngToSearch) Then
'                    Exit Function
'                End If
'        End If
'    Next

End Function

and here is the mini-spreadsheet

Budget.xlsm
	A	B	C	D
1	Description	Category		List of Categories
2	XllCricket	#VALUE!		Tennis
3	tt9Golf	#VALUE!		Cricket
4	New Cricket	#VALUE!		Hockey
5	LyyTennisT	Tennis		Basket Ball
6	Hockey	#VALUE!		Golf
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=SearchArr(A2,$D$2:$D$6)

Thanks heaps. Also note that i am open to suggestions if you think a custom formula is a stupid way to go about my problem

 

[Fluff]

Personally I'd use the formula in col C

+Fluff 1.xlsm
	A	B	C	D
1	Description	Category		List of Categories
2	XllCricket	Cricket	Cricket	Tennis
3	tt9Golf	Golf	Golf	Cricket
4	New Cricket	Cricket	Cricket	Hockey
5	LyyTennisT	Tennis	Tennis	Basket Ball
6	Hockey	Hockey	Hockey	Golf
Test

Cell Formulas
Range	Formula
B2:B6	B2	=SearchArr(A2,$D$2:$D$6)
C2:C6	C2	=LOOKUP(2,1/(ISNUMBER(SEARCH($D$2:$D$6,A2))),$D$2:$D$6)

But for your UDF you need to use

If IsError(Application.Search(c.Value, StrToMatch)) Then

 

[Fluff]

Alternatively use the VBA Instr function like

    For Each c In RngToSearch
        If InStr(1, StrToMatch, c.Value, vbTextCompare) > 0 Then
           SearchArr = c.Value
           Exit Function
        End If
    Next c

 

[C_Rieker]

Alternatively use the VBA Instr function like

    For Each c In RngToSearch
        If InStr(1, StrToMatch, c.Value, vbTextCompare) > 0 Then
           SearchArr = c.Value
           Exit Function
        End If
    Next c

This works perfectly thank you
The other solutions you posted also worked but i didnt fully understand how they worked haha.

 

[Peter_SSs]

The other solutions you posted also worked but i didnt fully understand how they worked haha.

What about this standard worksheet function? (assumes at most one match from column D could occur in the column A text as per your sample)

21 04 03.xlsm
	A	B	C	D
1	Description	Category		List of Categories
2	XllCricket	Cricket		Tennis
3	tt9Golf	Golf		Cricket
4	New Cricket	Cricket		Hockey
5	LyyTennisT	Tennis		Basket Ball
6	Hockey	Hockey		Golf
Check List

Cell Formulas
Range	Formula
B2:B6	B2	=FILTER(D$2:D$6,ISNUMBER(SEARCH(D$2:D$6,A2)),"")

 

[C_Rieker]

What about this standard worksheet function? (assumes at most one match from column D could occur in the column A text as per your sample)

21 04 03.xlsm
	A	B	C	D
1	Description	Category		List of Categories
2	XllCricket	Cricket		Tennis
3	tt9Golf	Golf		Cricket
4	New Cricket	Cricket		Hockey
5	LyyTennisT	Tennis		Basket Ball
6	Hockey	Hockey		Golf
Check List

Cell Formulas
Range	Formula
B2:B6	B2	=FILTER(D$2:D$6,ISNUMBER(SEARCH(D$2:D$6,A2)),"")

I like the direction of this approach. Unfortunately in my full spreadsheet (40k rows) that needs filtering, some of the descriptions have 3 or 4 matches. For the purpose of this post i just grabbed a small sample to make my question a little clearer. It would have probably been easier for myself had it been only 1 match.

 

[Peter_SSs]

some of the descriptions have 3 or 4 matches.

What result do you want returned when that happens?

Perhaps just one of the matches?

21 04 03.xlsm
	A	B	C	D
1	Description	Category		List of Categories
2	XllCricket & Golf	Cricket		Tennis
3	tt9Golf & Cricket	Cricket		Cricket
4	New Cricket	Cricket		Hockey
5	LyyTennisTGolf, Cricket	Tennis		Basket Ball
6	Hockey	Hockey		Golf
Check List

Cell Formulas
Range	Formula
B2:B6	B2	=INDEX(FILTER(D$2:D$6,ISNUMBER(SEARCH(D$2:D$6,A2)),""),1)

Perhaps all of the matches like this?

21 04 03.xlsm
	A	B
1	Description	Category
2	XllCricket & Golf	Cricket, Golf
3	tt9Golf & Cricket	Cricket, Golf
4	New Cricket	Cricket
5	LyyTennisTGolf, Cricket	Tennis, Cricket, Golf
6	Hockey	Hockey
Check List (2)

Cell Formulas
Range	Formula
B2:B6	B2	=TEXTJOIN(", ",1,FILTER(D$2:D$6,ISNUMBER(SEARCH(D$2:D$6,A2)),""))

Perhaps all of the matches like this?

21 04 03.xlsm
	A	B	C	D	E	F	G	H
1	Description		List of Categories		Category
2	XllCricket & Golf		Tennis		Cricket	Golf
3	tt9Golf & Cricket		Cricket		Cricket	Golf
4	New Cricket		Hockey		Cricket
5	LyyTennisTGolf, Cricket		Basket Ball		Tennis	Cricket	Golf
6	Hockey		Golf		Hockey
Check List (3)

Cell Formulas
Range	Formula
E5:G5,E4,E6,E2:F3	E2	=TRANSPOSE(FILTER(C$2:C$6,ISNUMBER(SEARCH(C$2:C$6,A2)),""))
Dynamic array formulas.