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 […]