vlookup seems a bit weak

[cdn-slasher]

I have some excel experts promoting what a great function the Vlookup is. But i think i must be missing something.
- having to keep the data in the table_array you are matching in the leftmost column is overly restrictive. it can force you to keep data in an illogical order (i.e. the column you are returning has to be maintained to the right of the column you are matching)
- using a column_index_no as an offset into the array also seems cumbersome. With large tables you have to count the columns and more importantly if you insert a column later-on the formula breaks.
- the syntax and logic of the sumif function appears so much better; you simply identify the column that you want to search and the column whose result you want to return.
- you don't have to count columns,
- you can order your columns any way you want and
- the formula doesn't break if you insert a column

What am i missing ? (or is the vlookup a weak cousin to the sumif)

[Bob Phillips]

You can manage the count columns and insert columns issue easily

=VLOOKUP(A2,lookupTable,MATCH("Location",INDEX(lookupTable,1,0),0),FALSE)

[cdn-slasher]

Thank you Bob! I WAS missing something. I wish i had understood this MATCH function before. I was manually editing the formula with the new column number every time i inserted a column. This will save me a ton of work the next time. Thanks again.

[apostolos55]

and ... present you GETIF()

GetIf() will now work with arrays and in some point with inequalities

syntax: GetIf (Range1;Criteria;[Range2];[ArraySize])
in sort: finds n (n<=arraysize) ranges2() so that ranges1()=criteria

It does what you can do with V/Hlookup and Match and some more functions, but simpler. Advanced users with array experience have probably solved such issues and dont need GetIf().
Users unfamiliar with Arrays will find it very useful.

It will work in simple or Array form. See notes and examples for usage in a typical Excel database, in conjunction with the Offset()

' 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

look for "getif advanced" for download with some examples in english but regular maintainance and fixes would at site of Michanikos.gr (look for getif v3.00)