Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Multiple Values in Same Range
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple lookup values and adding multiple rates across together | Excel Worksheet Functions | |||
How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
Lookup multiple values on multiple sheets | Excel Programming | |||
How do you Sum the range of two lookup values? | Excel Programming |