Lookup Multiple Values in Same Range

G

google

I have cobbled together from others' success an user defined function
to lookup values in a range as in:

Function myfunc(ra As Range, Tester)
Application.Volatile
For Each ce In ra
If ce.Value = Tester Then
holder = holder & Left(ce.Offset(0, 1), 10) & " " &
((ce.Offset(0, 2)) / 60) & Chr(10)
End If
myfunc = Left(holder, Len(holder) - 1)
Next ce
End Function

I insert this function in a column next to a column of comparison
values as in:

Sheet1
[cell][value]
[A1][Criteria1]
[A2][Criteria2]
[A3][Criteria3]
[A4][Criteria4]

[B1][myfunc(Data!A1:A8,A1)]
[B2][myfunc(Data!A1:A8,A2)]
[B3][myfunc(Data!A1:A8,A3)]
[B4][myfunc(Data!A1:A8,A4)]

Where the Data sheet looks like this:

[A1][Criteria1]
[A2][Criteria2]
[A3][Criteria1]
[A4][Criteria4]
[A5][Criteria1]
[A6][Criteria2]
[A7][Criteria3]
[A8][Criteria1]

I would expect that for each B column containing the user defined
function in Sheet1 a list of matched values from Data sheet would be
displayed. In the example above, Sheet1!B1 would actually display the
offset values for Data!A1, A3, A5, A8 as expected. However, none of the
subsequent Sheet1 / Column B functions return any matches for their
respective criteria.

I have played around with sort order on the Data sheet and that simply
returns a different, but still single result. It is as if there is an
index being retained from the first execution of the function that
needs to be reset. Is there some way to reset the range or otherwise
get the subsequent lookups to process and display results? I've
tinkered for hours and crawled the postings but can't quite see the
forest for the trees anymore.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top