Results 1 to 4 of 4

Thread: vlookup seems a bit weak

  1. #1

    vlookup seems a bit weak



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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)

  2. #2
    You can manage the count columns and insert columns issue easily

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

  3. #3
    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.

  4. #4
    Neophyte apostolos55's Avatar
    Join Date
    Jul 2012
    Location
    Greece
    Posts
    1
    Articles
    0

    vlookup seems a bit weak ... couldn't agree more

    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()

    Code:
    ' 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)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •