```
' EDIT_3 (Jun 2012): Few MAJOR changes.
' Added "Optional ArraySize As Long = 1"
' now: a = range to look into for match (could be range/ranges/array/name)
' b = lime to look for OR lime with comparison elements (ex: >=2)
' c Optional: If c exists then GetIf will return items from c (range/array...) where b matches a
' if c is missing then GetIf returns the indexes of matches found
' ArraySize Optional: Default = 1 : return 1 result
' special = -1 : find quantity of matches through CountIF and return so many results
' normal 1 to...: find as many as exist in an array as big as Asked!
' When covering an array of N elements with GetIf it is especially useful to send an
' Arraysize of N since it will return 0 elemements in empty slots while (-1) or a
' smaller number will return #N/A in empty slots.
' GetIf will always return a Vertical Array of elements according to ArraSize {getif(N,1)}. Can be used normaly
' for one result (default) without Array experience. Use Traspose() of Excel to convert to Horizontal
' ex: transpose( GetIf(N,1) ) = GetIf(1,N)
'
' TIPS: Use with excel formulas: Offset(),Index(),Smaller(),Larger(),Rows(1:N),Columns(1:N) for unlimited usage.
' See examples
'
' Original (Apr 2008)
' Syntax exactly like SUMIF, that is "GetIf (Range1;Criteria;Range2)"
' and finds range2(i) so that range1(i)=criteria
' No controls/checks, no speed or other improvements made by function
'
' Goylandris Apostolos
'
Public Function GetIf(a, b As String, Optional c, Optional ArraySize As Long = 1)
Dim Counter As Long, Counter2 As Long, Counter3 As Long, dum1, EvalFL As Boolean, matchFL As Boolean
Dim Counters() As Long, Holder()
' Fix ArraySize
If ArraySize = 0 Then ArraySize = Evaluate(Application.WorksheetFunction.CountIf(a, B))
If ArraySize < 1 Then ArraySize = 1 ' Restore to 1 even if evaluate finds 0 to avoid errors
' properly allocate arrays for VERTICAL results. Use TRANSPOSE() in excel fo HORIZONTAL
ReDim Counters(1 To ArraySize, 1 To 1), Holder(1 To ArraySize, 1 To 1)
' Enable trapping for Greater than or Smaller than functions
If InStr(b, ">") > 0 Then EvalFL = True
If InStr(b, "<") > 0 Then EvalFL = True
Counter = 0: Counter2 = 0: Counter3 = 0
For Each dum1 In a ' Search for index
Counter = Counter + 1: matchFL = False
If EvalFL Then
If Evaluate(Format(dum1, "#0") + B) Then matchFL = True
Else
If b = Trim(dum1) Then matchFL = True
End If
If matchFL Then
Counter3 = Counter3 + 1
Counters(Counter3, 1) = Counter
If Counter3 = ArraySize Then Exit For 'Search only as many as asked for
End If
Next
If Counter3 > 0 Then ' if something was found
If IsMissing(c) Then ' Edit:Jun2012 Ommitting range2 (c) returns Position within range
GetIf = Counters
Else
Counter = 1
For Each dum1 In c ' Use index to find equivalent
Counter2 = Counter2 + 1
If Counter2 = Counters(Counter, 1) Then
Holder(Counter, 1) = dum1
Counter = Counter + 1
If Counter > Counter3 Then Exit For 'Search only as many as asked for
End If
Next
GetIf = Holder
End If
Else
GetIf = "-":
End If
End Function
```