Function FilterData(x As Range, c As Variant, d As Variant) As Variant
Dim filtered_nrows As Long
Dim filtered_Data() As Variant
Dim Numrows As Long
Dim Rows As Long
Dim i As Long

' Determine the number of rows that meet the criteria
filtered_nrows = numbrows(x, c, d)

' If no rows meet the criteria, return an empty array
If filtered_nrows = 0 Then
    FilterData = Array()
    Exit Function
End If

' Resize the array to hold the filtered data
ReDim filtered_Data(1 To filtered_nrows, 1 To 4)

' Get the total number of rows in the range
Numrows = x.Rows.count
Rows = 1

' Loop through each row in the range
For i = 1 To Numrows
    ' Check if the current row meets the criteria
    If LCase(x.Cells(i, 1).Value) = LCase(c) And x.Cells(i, 2).Value = d Then
        ' Populate the filtered data array with specific columns
        filtered_Data(Rows, 1) = x.Cells(i, 6).Value
        filtered_Data(Rows, 2) = x.Cells(i, 4).Value
        filtered_Data(Rows, 3) = x.Cells(i, 13).Value
        filtered_Data(Rows, 4) = x.Cells(i, 9).Value
        Rows = Rows + 1
    End If
Next i

' Return the filtered data array
FilterData = filtered_Data

End Function

Function numbrows(tableRange As Range, criteria As Variant, criteria2 As Variant) As Long
Dim filteredRows As Long
On Error Resume Next
filteredRows = Application.WorksheetFunction.CountIfs( _
Application.WorksheetFunction.Index(tableRange, 0, 1), criteria, _
Application.WorksheetFunction.Index(tableRange, 0, 2), criteria2)
On Error GoTo 0
numbrows = filteredRows
End Function

Simply trying to filter out a data in a range and output specific columns from the row, tried changing it to a dynamic resizing code but that seems to provide a value error chat gpt was not helpful as well. Seems like it only does not work when the criteria run is met for only one row like the images here. enter image description here. enter image description here.

For ease of use the formula is meant to work even if the range i input it is beyond the dimension of my defined array. Thus, it should be N/A values beyond that one row for this case.