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.
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
sAssembly = vAssembly
‘find the first occurance
Set rAfter = rLookup.Columns(1).Cells(rLookup.Columns(1).Cells.Count)
Set rFound = rLookup.Columns(1).Find(sAssembly, _
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
If IsMissing(vIfError) Then
vErrReturn = CVErr(xlErrNA)
vErrReturn = vIfError
If rFound Is Nothing Then
CLOOKUP = vErrReturn
CLOOKUP = rFound.Offset(0, lColumn – 1).Value2
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.