I have an excel file with a table in a sheet called Category.
Columns are the months of 2024 and rows are unique Customer IDs.
The table shows if the customers are “New”, “Active” of “Out of Scope” for each month.
I am trying to create on a separate sheet a dynamic list that takes into argument the month, and shows the IDs of the customers that are

1. “New” for the selected month
2. All previous months are “out of scope”.

The function should display a list, which length will change depending on the month.

I have come up with this function and a sub that works perfectly in the immediate window, but when typing the function in my excel sheet as an array formula, it just creates an array of #VALUE.

I tried to get some answers from chatgpt and different forums but I can’t resolve the issue.

Function NewCustomer(month As String) As Variant()
Dim wsCategory As Worksheet
Dim CustomerID As String
Dim CustomerIDRange As Variant
Dim MonthCol As Long
Dim result As Variant
Dim tablerange As Variant
Dim i As Long, j As Long
Dim resultindex As Long

 On Error GoTo ErrorHandler

Set wsCategory = ThisWorkbook.Sheets("Category")
Set tablerange = wsCategory.Range("B1").CurrentRegion
Set CustomerIDRange = tablerange.Columns(2)


On Error Resume Next
MonthCol = wsCategory.Rows(2).Find(What:=month, LookIn:=xlValues, LookAt:=xlWhole).Column
On Error GoTo 0

If MonthCol = 0 Then
    NewCustomer = Array("no month")
    Exit Function
End If

ReDim result(1 To tablerange.Rows.Count - 2)

resultindex = 0

For i = 3 To tablerange.Rows.Count
    If wsCategory.Cells(i, MonthCol).Value = "New" Then
        Dim AllPreviousOutOfScope As Boolean
        AllPreviousOutOfScope = True
  
        
        For j = 4 To MonthCol - 1
            If wsCategory.Cells(i, j).Value <> "Out Of Scope" Then
                AllPreviousOutOfScope = False
                Exit For
            End If
        Next j
        
        If AllPreviousOutOfScope Then
           resultindex = resultindex + 1
           result(resultindex) = CustomerIDRange.Cells(i)
           
           
        End If
    End If
Next i



If resultindex = 0 Then
    NewCustomer = Array("no new customers")
Else

    ReDim Preserve result(1 To resultindex)
    NewCustomer = result
End If
 
 Exit Function

ErrorHandler:
NewCustomer = Array(“error”)

Dim result2 As Variant
result2 = result

End Function