' GETIF
' syntax: getif(range1;criteria;[range2];[ArraySize])
' now: A:range1 = range to look into for match (could be range/ranges/array/name)
'      B:criteria = lime to look for
'      C:range2 Optional: If range2 exists then GetIf will return items from range2 (range/array...) where
'                           ...criteria matches range1
'                       if range2 is missing then GetIf returns the indexes of matches found
'      ArraySize Optional: Default =  1 : return 1 result
'                          special = 0 : find quantity of matches and return as many results
'                          normal 1 to...: find as many as exist in an array as big as Asked!
' * When expecting results in array of N elements from GetIf, it is especially useful to send an
'   Arraysize of N since it will return 0 elemements in empty slots while (0) 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
'
' EDIT_3 (Jun 2012): Few MAJOR changes.
' Working and returning Vertical Arrays. HOWEVER Inputs no need to be Vertical Arrays or Arrays at all...
' Added "Optional ArraySize As Long = 1"
'
' EDIT_2 (Jun 2012): range2 made Optional. If range2 is missing then return
' the Counter found so that range1(Counter)=criteria, then use Counter with Offset or Index functions!!!
' Obsolete: GoRandom has gone bye bye... complicated things
'
' EDIT_1
' (criteria) became (criteria as string) as results where not always as anticipated. Seems to function properly now
' Osbolete: GoRandom added for specific randomize purposes
'
' 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
'
' Apostolos55
'
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, matchFL As Boolean
Dim Counters() As Long, Holder()

' Fix ArraySize
If ArraySize = 0 Then
    Counter = 0: For Each dum1 In A
        If B = Trim(dum1) Then Counter = Counter + 1
    Next: ArraySize = Counter: End If
If ArraySize < 1 Then ArraySize = 1     ' Restore to 1 even if evaluate finds 0 to avoid errors

' properly allocate arrays for VERTICAL results.
ReDim Counters(1 To ArraySize, 1 To 1), Holder(1 To ArraySize, 1 To 1)

Counter = 0: Counter2 = 0: Counter3 = 0
For Each dum1 In A                  ' Search for index
    Counter = Counter + 1: matchFL = False
    If B = Trim(dum1) Then matchFL = True
    If matchFL Then
        Counter3 = Counter3 + 1
        Counters(Counter3, 1) = Counter         'Log found locations to Counters()
        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 found locations 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