Lookup Second Occurrence

I need to do a VLOOKUP but find occurrences later than the first one. I’ve listed out all of my assemblies and all of their component parts like so:

Parent Child Quantity

To list the the children of a particular parent, I need a function that can find multiple occurrences of the parent. I considered a pivot table, but it’s just too bulky to shoehorn into my existing template. I also considered an advanced filter, but I need to limit the list to the first 10 children.

I settled on a UDF. That way I could call it form a worksheet or from code if the need arises. I called it CLOOKUP for component lookup since I’m looking up raw materials components that make up a finished assembly.

Public Function CLOOKUP(vAssembly As Variant, _
    rLookup As Range, _
    lColumn As Long, _
    lOccurance As Long, _
    Optional vIfError As Variant) As Variant
   
    Dim sAssembly As String
    Dim rFound As Range
    Dim i As Long
    Dim sFirstAddress As String
    Dim rAfter As Range
    Dim vErrReturn As Variant
   
    If TypeName(vAssembly) = “Range” Then
        sAssembly = vAssembly.Value2
    Else
        sAssembly = vAssembly
    End If
   
    ‘find the first occurance
   Set rAfter = rLookup.Columns(1).Cells(rLookup.Columns(1).Cells.Count)
    Set rFound = rLookup.Columns(1).Find(sAssembly, _
        rAfter, _
        xlValues, _
        xlWhole)
   
    If Not rFound Is Nothing Then
        If lOccurance > 1 Then
            sFirstAddress = rFound.Address
            For i = 2 To lOccurance
                Set rFound = rLookup.Columns(1).Find(sAssembly, rFound, xlValues, xlWhole)
                If rFound.Address = sFirstAddress Then ‘if not enough occurances, return NA
                   Set rFound = Nothing
                    Exit For
                End If
            Next i
         End If
    End If
   
    If IsMissing(vIfError) Then
        vErrReturn = CVErr(xlErrNA)
    Else
        vErrReturn = vIfError
    End If
   
    If rFound Is Nothing Then
        CLOOKUP = vErrReturn
    Else
        CLOOKUP = rFound.Offset(0, lColumn – 1).Value2
    End If
   
End Function

The first three arguments are the same as VLOOKUP. The fourth argument is the occurrence of vAssembly in the list. It will return NA if asked for an occurrence that doesn’t exist. Finally I added an optional IfError argument. Errors default to NA like VLOOKUP, but I didn’t want to have to wrap all these calls in =IF(ISNA(..)).

In the first section I test to see if vAssembly is a Range object and store its Value2 property if it is. Since the Value property is the default property of Range, this is really not necessary. I’m trying to get in the habit of using Value2, even though it’s meaningless in the this context.

Next I use the Find method to find all of the occurrences of vAssembly. If I run out, I set rFound back to Nothing and that’s my trigger for returning an error. I wasn’t able to use FindNext in a UDF. Mike_R says:

Yeah, the list of commands that can modify the cursor and therefore are off limits to User Defined Function (UDFs) include: SpecialCells, CurrentRegion, CurrentArray, Find (xl 9.0 and below), Replace, GOTO, SELECT, PRECEDENTS, AutoFilters, AdvancedFilters, etc..

I’m surprised I’ve never run into that before. Or maybe I have and just forgot. So I just use Find again and I have to repeat the arguments. No big whoop.

Next I see if vIfError is specified and set the appropriate return error value. Finally, I test if rFound is Nothing and return the offset or the error value.

There’s a little work to do on this function, such as if the column is beyond the lookup range. I’d also like to be able to pass in an array for the lookup range, but this works for now.

22 Comments

  1. You could use an Advanced Filter and return the first 10 qualified records.
    Assuming data on a sheet named Data, the criteria range would be:

    A1: Parent B1: (blank)
    A2: NameOfParent B2: =COUNTIF(Data!$A$1:A2,Data!A2)

  2. JM says:

    I usually use some form of INDEX and SMALL in an array formula:

    =INDEX(B1:B10,SMALL(IF(A1:A10=A2,ROW(A1:A10)),X)-MIN(ROW(A1:A10))+1)

    where B1:B10 is the home of the value to return, A1:A10 is range to evaluate, and X represents the occurrence desired.

  3. I usually use Application.WorksheetFunction.VLookup or Match rather than .Find because its faster and works in all Excel versions, and because if the data is sorted you can just locate the first and last and then handle the data as a block.

    And I like array UDFs because they are so efficient, so I would rewrite to return a 2D array of 10×1 answers, then wrap the UDF inside an INDEX if I only wanted one particular Occurance.

  4. DD: The main reason I eschewed advanced filter is because I expect to need it in code.
    JM: I like it, but it makes my formula so unwieldy checking for errors and such.
    CW: I have one spot where I return the first 10 and another where I return the first six. Can I still do it as one array formula?

  5. Option Explicit
    Option Base 1
    Public Function CLOOKUPS(vAssembly As Variant, _
                             rLookup As Range, _
                             lColumn As Long, _
                             Optional vIfError As Variant) As Variant

        Dim sAssembly As String
        Dim rFound As Range
        Dim i As Long

        Dim vErrReturn As Variant

        Dim vArr1 As Variant
        Dim vArr2 As Variant
        Dim jFirstRow As Long
        Dim jLastRow As Long
       
       ‘ assumes data is sorted in ascending assembly order
       
       If TypeName(vAssembly) = “Range” Then
            sAssembly = vAssembly.Value2
        Else
            sAssembly = vAssembly
        End If
       
       ‘ set  not found error
       
       If IsMissing(vIfError) Then
            vErrReturn = CVErr(xlErrNA)
        Else
            vErrReturn = vIfError
        End If

        On Error GoTo NoMatch
        ‘find the first occurance (unsorted Match)
       jFirstRow = Application.WorksheetFunction.Match(sAssembly, rLookup.Columns(1), 0)
        ‘ find the last occurance (sorted ascending Match)
       jLastRow = Application.WorksheetFunction.Match(sAssembly, rLookup.Columns(1), 1)

        vArr1 = rLookup.Resize(10, 1).Offset(jFirstRow – 1, lColumn – 1).Value2
       
        If jLastRow – jFirstRow &lt. 9 Then
            For i = jLastRow – jFirstRow + 2 To 10
                vArr1(i, 1) = vErrReturn
            Next i
        End If
       
    Finish:
        CLOOKUPS = vArr1
        Exit Function
    NoMatch:
        vArr1 = vErrReturn
        GoTo Finish
    End Function

  6. If you enter the array UDF into just 6 cells it will only return the first 6.
    If you enter it into 15 cells the excess will be filled with #N/A.

    Or you could use Application.Caller.rows.count to find out how many rows the UDF was entered into.

  7. fzz says:

    As a general matter, if you have a udf that returns a variable size array, you can select the subarray of, say, the first 6 items with

    =LOOKUP({1;2;3;4;5;6},{1;2;3;4;5;6},yourudfhere(yourargumentshere))

    and, FWIW, if you wanted the 2nd, 4th and 6th, you could use

    =LOOKUP({2;4;6},{1;2;3;4;5;6},yourudfhere(yourargumentshere))

    This is the only way I know of to use subarrays as expressions within larger formulas entered into single cells (i.e., Application.Caller doesn’t always indicate the needed size of the udf’s array result).

  8. Brenda says:

    Well, I gagged on the first Office Community Clip from the Excel section, chosen at random, I viewed and turned off the second one. These people submitting these clips need to find the help button F1 or Help on the toolbar (I have no idea where it is on the fluent ribbon), use it and then read what is displayed. A video requires something to say and a voice to say it. Even some of the web available videos from top notch sites with excellent subject matter, are no good if the presenter does not have a voice for presentation. The time spent making these awful videos could have been better spent reading the help material or a basic Excel book.

    And why not take advantage of the supplied help and available web sites with knowledgeable people, such as Microsoft MVPs who generously take the time to submit material that is relevant, thought out, backed up by experience and the company that programed Excel in the first place.

    Although, considering the “fluent ribbon” I am beginning to have doubts about what is going on.

  9. John Michl says:

    I’ve used a combination of SUMPRODUCT and then MATCH and INDEX to perform something similar. I have a situation where I receive budget projection updates on a somewhat random basis. For some projects I could get two in one month or perhaps none. I need to produce a report that always shows the three most recent projects for each project regardless of the date of the projections. In the data sheet, I have a column labels PrjVersion with a SUMPRODUCT formula that numbers the projections for each project with 1 being most recent. The formula is something like this: = SUMPRODUCT((A2=ProjNumber_rng)*(B2

  10. sam says:

    ” I like it, but it makes my formula so unwieldy checking for errors and such.”

    Not if you use IFERROR(Long Formula, “NotFound”)

    For Excel 2003 use the XLM version available on Simons blog

  11. Niek Otten says:

    In the newsgroups there is often a request for the last occurrence, without knowing how many there are.
    Maybe occurrence # zero could indicate “last one”?

  12. Brett says:

    DK,

    I cannot get this to work. I’m on XL2000. Is that going to be a problem?

    Many thanks,
    Brett

  13. fzz says:

    Just read the udf more closely. Some comment needed on the code.

    On Error GoTo NoMatch
    :
    Finish:
        CLOOKUPS = vArr1
        Exit Function
    NoMatch:
        vArr1 = vErrReturn
        GoTo Finish
    End Function

    Spaghetti! Why not something direct?

    On Error GoTo NoMatch
    :
    NoMatch:
        CLOOKUPS = IIf(Err.Number = 0, vArr1, vErrReturn)
    End Function
  14. fzz says:

    To clarify, that was Charles Williams’s code.

  15. Brett: I haven’t used 2000 in a while, so I’m not sure what might be causing the problem. Did the Find method change after 2000? Try checking help on the Find method and see if the order of the arguments changed or something.

  16. fzz says:

    IIRC, Excel 97 and 2000 just can’t handle range class’s Find method in a UDF. I think the Find method in those versions tried to change the active cell, which would choke a udf.

  17. Brett says:

    Thanks for the ideas. The udf always returns the error, optional or default. I’ll comment of what I find.

    Brett

  18. Brett says:

    It looks like it is a version issue.

    http://www.xtremevbtalk.com/showthread.php?p=1296419

    Brett

  19. T says:

    Brett, here’s what I came up with that seems to work for XL 2000. I started with CW’s, didn’t change it much from there, but some parts didn’t work. I’m lousy in VBA, so I don’t know that this is particularly lean or robust…

    Option Explicit
    Option Base 1
    Public Function CLOOKUP(Lookup_value As Variant, _
                            Table_array As Range, _
                            Col_index_num As Long, _
                            Optional vIfError As Variant) As Variant

        Dim sAssembly As String
        Dim rFound As Range
        Dim i As Long

        If TypeName(Lookup_value) = “Range” Then
            sAssembly = Lookup_value.Value2
        Else
            sAssembly = Lookup_value
        End If

        Dim vErrReturn As Variant
        Dim c1 As Range
        Dim c2 As Range
        Dim c3 As Range
        Dim nrows As Integer
        Set c1 = Table_array.Columns(1)
        nrows = c1.Rows.Count

        Dim vArr1() As Variant
        ReDim vArr1(1 To nrows, 1 To 2) As Variant

        Dim jFirstRow As Long
        Dim jnextrow As Long
        Dim jLastRow As Long
        Dim jCurrent As Integer

        ‘ set  not found error
       If IsMissing(vIfError) Then
            vErrReturn = CVErr(xlErrNA)
        Else
            vErrReturn = vIfError
        End If

        On Error GoTo NoMatch

        ‘find the first occurrence (unsorted Match)
       jFirstRow = Application.WorksheetFunction.Match(Lookup_value, c1, 0)
        vArr1(1, 1) = Table_array.Cells(jFirstRow, Col_index_num).Value
        vArr1(1, 2) = 1
        jCurrent = jFirstRow
       
        ‘ find rest of occurrences, no sorting needed?
       Dim X As Integer
        X = 2
        On Error Resume Next
        Do While jCurrent  0
            Set c2 = Range(c1.Cells(jCurrent + 1, 1), c1.Cells(nrows, 1))
            Set c3 = Range(c1.Cells(jCurrent + 1, 1), c1.Cells(nrows, 1).Offset(0, 1))
            jnextrow = 0
            jnextrow = Application.WorksheetFunction.Match(Lookup_value, c2, 0)
            If jnextrow = 0 Then jCurrent = 0
            jCurrent = jnextrow + jCurrent
            If jCurrent  0 Then vArr1(X, 1) = c3.Cells(jnextrow, Col_index_num).Value
            If jCurrent  0 Then vArr1(X, 2) = X
            X = X + 1
        Loop
       
        For i = X – 1 To 10
            vArr1(i, 1) = vErrReturn
            vArr1(i, 2) = i
        Next i
       
    Finish:
        CLOOKUP = vArr1

        Exit Function
    NoMatch:
        For i = 1 To 10
            vArr1(i, 1) = vErrReturn
            vArr1(i, 2) = vErrReturn
        Next i
        GoTo Finish

    End Function

  20. Brett says:

    T,

    Thanks very much!

    Brett

  21. DM/Diddy says:

    A little aside on the Value2 property – this bit me today on a workbook that has been used by a dozen people for at least a year.

    Enter $0.0001 in a cell, type ?selection.value or value2 in the immediate window. Both return 0.0001.

    Increase the decimal to $0.00001 and .value returns 0, while .value2 returns 0.00001.

    Moral of the story: always use .Value2.

  22. […] are other ways of looking up multiple criterias in Excel such as arrays. This is by far the easiest way of getting the job […]

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: