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
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.
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)
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.
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.
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?
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
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.
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).
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.
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
” 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
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”?
DK,
I cannot get this to work. I’m on XL2000. Is that going to be a problem?
Many thanks,
Brett
Just read the udf more closely. Some comment needed on the code.
:
Finish:
CLOOKUPS = vArr1
Exit Function
NoMatch:
vArr1 = vErrReturn
GoTo Finish
End Function
Spaghetti! Why not something direct?
:
NoMatch:
CLOOKUPS = IIf(Err.Number = 0, vArr1, vErrReturn)
End Function
To clarify, that was Charles Williams’s code.
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.
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.
Thanks for the ideas. The udf always returns the error, optional or default. I’ll comment of what I find.
Brett
It looks like it is a version issue.
http://www.xtremevbtalk.com/showthread.php?p=1296419
Brett
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 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
T,
Thanks very much!
Brett
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.
[…] are other ways of looking up multiple criterias in Excel such as arrays. This is by far the easiest way of getting the job […]