VBA function to almost replicate xlookup but return array

So I have two columns Material, and Process. The user selects a material and i need a function to look in a different sheet for all process matches to that material. Example would be a user picks Steel, and the the value returned is an array containing all processes that have the material steel. This shouldn't be too hard for someone better than I am at VBA coding, but I'm lost. Below is what ive tried and it just returns "0"

Function dependentSearch(Phrase As String) As Variant
Dim returnArray As Variant
Dim I As Long

Sheet6.Activate

' Get last row count
Dim last_row As Long
last_row = Cells(Rows.Count, "O").End(xlUp).Row

' Loop through rows in sheet6
For I = 1 To last_row
    ' Check if material matches and if so store value from the adjacent cell
    If Phrase = Range("C" & I).Text Then
        returnArray = returnArray + Range("D" & I)
    End If
Next I


dependentSearch = returnArray

End Function

Hi there,

Take a look at the attached workbook and see if it gets you moving in the right direction. It uses the following code in a standard VBA CodeModule:

Option Explicit


'=========================================================================================
'=========================================================================================


Public Const gsMATERIAL     As String = "ptrMaterial"

Const msFIRST_PROCESS_CELL  As String = "ptrFirstProcess"


'=========================================================================================
'=========================================================================================


Sub UpdateProcessList(sMaterial As String)

    Dim vaProcesses As Variant
    Dim iNoOfRows   As Integer

    Call ClearProcessList

    vaProcesses = mvaProcesses(sMaterial:=sMaterial)

    iNoOfRows = UBound(vaProcesses)

    With wksMaterials.Range(msFIRST_PROCESS_CELL)

        Range(.Cells(1, 1), _
              .Cells(iNoOfRows, 1)).Value = vaProcesses

    End With

End Sub


'=========================================================================================
'=========================================================================================


Private Sub ClearProcessList()

    Dim rFirstCell  As Range
    Dim rLastCell   As Range

    Set rFirstCell = wksMaterials.Range(msFIRST_PROCESS_CELL)

    If rFirstCell.Offset(1, 0).Value <> vbNullString Then
          Set rLastCell = rFirstCell.End(xlDown)
    Else: Set rLastCell = rFirstCell
    End If

    Range(rFirstCell, rLastCell).ClearContents

End Sub


'=========================================================================================
'=========================================================================================


Private Function mvaProcesses(sMaterial As String) As Variant

    Dim rMaterialCells  As Range
    Dim vaProcesses     As Variant
    Dim iProcessNo      As Integer
    Dim sProcess        As String
    Dim rCell           As Range

    Set rMaterialCells = mrMaterialCells()

    ReDim vaProcesses(1 To 1)
    iProcessNo = 0

    For Each rCell In rMaterialCells.Cells

        If rCell.Value = sMaterial Then

            iProcessNo = iProcessNo + 1

            sProcess = rCell.Offset(0, 1).Value

            If iProcessNo > 1 Then
                ReDim Preserve vaProcesses(1 To iProcessNo)
            End If

            vaProcesses(iProcessNo) = sProcess

        End If

    Next rCell

    mvaProcesses = WorksheetFunction.Transpose(vaProcesses)

End Function


'=========================================================================================
'=========================================================================================


Private Function mrMaterialCells() As Range

    Const sMATERIALS_COLUMN As String = "C"

    Dim rMaterialColumn As Range

    With wksProcesses

        Set rMaterialColumn = .Columns(sMATERIALS_COLUMN)

        Set mrMaterialCells = Intersect(.UsedRange, _
                                        rMaterialColumn)

    End With

End Function

And the following code in the VBA CodeModule of the "Materials" worksheet:

Option Explicit


'=========================================================================================
'=========================================================================================


Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.CountLarge = 1 Then

        If Target.Address = Me.Range(gsMATERIAL).Address Then
            Call UpdateProcessList(sMaterial:=Target.Value)
        End If

    End If

End Sub

The highlighted value may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M