Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Return Multiple Values in VBA
My problem requires returning multiple values through a lookup function in
VBA. My code is as follows: Do Pick = Application.WorksheetFunction.Small(Range_1, i) A = Application.WorksheetFunction.Hlookup(Pick,Range_2 , x, False) A1 = A1 + A i = i +1 Loop Until i = Z An alternative that comes to my mind is to get the address of the cell that contains the smallest i value in the array Range_1 and then use offset to get correspodning values from Range_2. However, I cant get the address of that cell. A close approach is as follows but it distrurbs the over all logic of my model: Do If Worksheets(2).Range("F71").Offset(0, Count).Value = Pick Then Addr = (Worksheets(2).Range("F71").Offset(0, Count).Address) A = Worksheets(2).Range(Addr).Offset(5, 0).Value A1= A1=A Count = Count +1 Loop Can any one help me with 1. To obtain the address of the cell with smallest i vale in Range_1 and repeating the process. Or 2. Lookup Returning multiple values in VBA Or anyother suitable approach which solves my problem. Many Thanks Mallick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Return Multiple Values in VBA
Try the below. 1st one returns value using MATCH and INDEX and the second one
using VLOOKUP...Assume the data layout in A1:C10... Sub GetValuesUsingINDEX() Dim Range_1 As Range Dim Range_2 As Range 'Assuming the range as below Set Range_1 = Range("A1:A10") Set Range_2 = Range("B1:B10") 'Using MATCH to return row and INDEX to reuturn the values= i = 1 Do pick = Application.WorksheetFunction.Small(Range_1, i) a = Application.WorksheetFunction.Match(pick, Range_1, 0) MsgBox Application.WorksheetFunction.Index(Range_2, a) i = i + 1 Loop Until i = Z End Sub Sub GetValuesUsingVLOOKUP() Dim Range_1 As Range 'Assuming the range as below Set Range_1 = Range("A1:C10") i = 1 Do pick = Application.WorksheetFunction.Small(Range_1, i) MsgBox Application.WorksheetFunction.VLookup(pick, Range_1, 2, False) MsgBox Application.WorksheetFunction.VLookup(pick, Range_1, 3, False) i = i + 1 Loop Until i = Z End Sub -- If this post helps click Yes --------------- Jacob Skaria "Mallick" wrote: My problem requires returning multiple values through a lookup function in VBA. My code is as follows: Do Pick = Application.WorksheetFunction.Small(Range_1, i) A = Application.WorksheetFunction.Hlookup(Pick,Range_2 , x, False) A1 = A1 + A i = i +1 Loop Until i = Z An alternative that comes to my mind is to get the address of the cell that contains the smallest i value in the array Range_1 and then use offset to get correspodning values from Range_2. However, I cant get the address of that cell. A close approach is as follows but it distrurbs the over all logic of my model: Do If Worksheets(2).Range("F71").Offset(0, Count).Value = Pick Then Addr = (Worksheets(2).Range("F71").Offset(0, Count).Address) A = Worksheets(2).Range(Addr).Offset(5, 0).Value A1= A1=A Count = Count +1 Loop Can any one help me with 1. To obtain the address of the cell with smallest i vale in Range_1 and repeating the process. Or 2. Lookup Returning multiple values in VBA Or anyother suitable approach which solves my problem. Many Thanks Mallick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Return Multiple Values in VBA
I have only modified your code...Please check out the undeclared variables....
If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below. 1st one returns value using MATCH and INDEX and the second one using VLOOKUP...Assume the data layout in A1:C10... Sub GetValuesUsingINDEX() Dim Range_1 As Range Dim Range_2 As Range 'Assuming the range as below Set Range_1 = Range("A1:A10") Set Range_2 = Range("B1:B10") 'Using MATCH to return row and INDEX to reuturn the values= i = 1 Do pick = Application.WorksheetFunction.Small(Range_1, i) a = Application.WorksheetFunction.Match(pick, Range_1, 0) MsgBox Application.WorksheetFunction.Index(Range_2, a) i = i + 1 Loop Until i = Z End Sub Sub GetValuesUsingVLOOKUP() Dim Range_1 As Range 'Assuming the range as below Set Range_1 = Range("A1:C10") i = 1 Do pick = Application.WorksheetFunction.Small(Range_1, i) MsgBox Application.WorksheetFunction.VLookup(pick, Range_1, 2, False) MsgBox Application.WorksheetFunction.VLookup(pick, Range_1, 3, False) i = i + 1 Loop Until i = Z End Sub -- If this post helps click Yes --------------- Jacob Skaria "Mallick" wrote: My problem requires returning multiple values through a lookup function in VBA. My code is as follows: Do Pick = Application.WorksheetFunction.Small(Range_1, i) A = Application.WorksheetFunction.Hlookup(Pick,Range_2 , x, False) A1 = A1 + A i = i +1 Loop Until i = Z An alternative that comes to my mind is to get the address of the cell that contains the smallest i value in the array Range_1 and then use offset to get correspodning values from Range_2. However, I cant get the address of that cell. A close approach is as follows but it distrurbs the over all logic of my model: Do If Worksheets(2).Range("F71").Offset(0, Count).Value = Pick Then Addr = (Worksheets(2).Range("F71").Offset(0, Count).Address) A = Worksheets(2).Range(Addr).Offset(5, 0).Value A1= A1=A Count = Count +1 Loop Can any one help me with 1. To obtain the address of the cell with smallest i vale in Range_1 and repeating the process. Or 2. Lookup Returning multiple values in VBA Or anyother suitable approach which solves my problem. Many Thanks Mallick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Return Multiple Values in VBA
Thanks Jacob for your post.
Now, I realize that i didnt put my case very clearly. Index-Match and VLookup are doing exactly what HLookup of my code was achieving. My actual problem is that any Lookup function finds the first value matching the criterion and return corresponding values. There are multiple matching values in my case and I need to obtain corresponding values of all the matching values. I even tried Find method of VBA bit it didnt help too. I will have to re-do my all model as I am not finding the way out of it :( However, if I am able to get the address associated with the following function, my all worries would be over :) Application.WorksheetFunction.Small(Range_1, i) Can you help me with this? Many thanks. Mallick "Jacob Skaria" wrote: Try the below. 1st one returns value using MATCH and INDEX and the second one using VLOOKUP...Assume the data layout in A1:C10... Sub GetValuesUsingINDEX() Dim Range_1 As Range Dim Range_2 As Range 'Assuming the range as below Set Range_1 = Range("A1:A10") Set Range_2 = Range("B1:B10") 'Using MATCH to return row and INDEX to reuturn the values= i = 1 Do pick = Application.WorksheetFunction.Small(Range_1, i) a = Application.WorksheetFunction.Match(pick, Range_1, 0) MsgBox Application.WorksheetFunction.Index(Range_2, a) i = i + 1 Loop Until i = Z End Sub Sub GetValuesUsingVLOOKUP() Dim Range_1 As Range 'Assuming the range as below Set Range_1 = Range("A1:C10") i = 1 Do pick = Application.WorksheetFunction.Small(Range_1, i) MsgBox Application.WorksheetFunction.VLookup(pick, Range_1, 2, False) MsgBox Application.WorksheetFunction.VLookup(pick, Range_1, 3, False) i = i + 1 Loop Until i = Z End Sub -- If this post helps click Yes --------------- Jacob Skaria "Mallick" wrote: My problem requires returning multiple values through a lookup function in VBA. My code is as follows: Do Pick = Application.WorksheetFunction.Small(Range_1, i) A = Application.WorksheetFunction.Hlookup(Pick,Range_2 , x, False) A1 = A1 + A i = i +1 Loop Until i = Z An alternative that comes to my mind is to get the address of the cell that contains the smallest i value in the array Range_1 and then use offset to get correspodning values from Range_2. However, I cant get the address of that cell. A close approach is as follows but it distrurbs the over all logic of my model: Do If Worksheets(2).Range("F71").Offset(0, Count).Value = Pick Then Addr = (Worksheets(2).Range("F71").Offset(0, Count).Address) A = Worksheets(2).Range(Addr).Offset(5, 0).Value A1= A1=A Count = Count +1 Loop Can any one help me with 1. To obtain the address of the cell with smallest i vale in Range_1 and repeating the process. Or 2. Lookup Returning multiple values in VBA Or anyother suitable approach which solves my problem. Many Thanks Mallick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Return Multiple Values in VBA
You can try out in this order
1. Identify the least using SMALL() 2. Get the number of instances using COUNTIF() 3. Loop for the number of instances ...Here is the key...the variable which should be changing within the loop is the starting row of the range ...which will be assigned to the previous result from MATCH() (ie; row + 1) 4. Within each loop get the values using INDEX. If this post helps click Yes --------------- Jacob Skaria "Mallick" wrote: Thanks Jacob for your post. Now, I realize that i didnt put my case very clearly. Index-Match and VLookup are doing exactly what HLookup of my code was achieving. My actual problem is that any Lookup function finds the first value matching the criterion and return corresponding values. There are multiple matching values in my case and I need to obtain corresponding values of all the matching values. I even tried Find method of VBA bit it didnt help too. I will have to re-do my all model as I am not finding the way out of it :( However, if I am able to get the address associated with the following function, my all worries would be over :) Application.WorksheetFunction.Small(Range_1, i) Can you help me with this? Many thanks. Mallick "Jacob Skaria" wrote: Try the below. 1st one returns value using MATCH and INDEX and the second one using VLOOKUP...Assume the data layout in A1:C10... Sub GetValuesUsingINDEX() Dim Range_1 As Range Dim Range_2 As Range 'Assuming the range as below Set Range_1 = Range("A1:A10") Set Range_2 = Range("B1:B10") 'Using MATCH to return row and INDEX to reuturn the values= i = 1 Do pick = Application.WorksheetFunction.Small(Range_1, i) a = Application.WorksheetFunction.Match(pick, Range_1, 0) MsgBox Application.WorksheetFunction.Index(Range_2, a) i = i + 1 Loop Until i = Z End Sub Sub GetValuesUsingVLOOKUP() Dim Range_1 As Range 'Assuming the range as below Set Range_1 = Range("A1:C10") i = 1 Do pick = Application.WorksheetFunction.Small(Range_1, i) MsgBox Application.WorksheetFunction.VLookup(pick, Range_1, 2, False) MsgBox Application.WorksheetFunction.VLookup(pick, Range_1, 3, False) i = i + 1 Loop Until i = Z End Sub -- If this post helps click Yes --------------- Jacob Skaria "Mallick" wrote: My problem requires returning multiple values through a lookup function in VBA. My code is as follows: Do Pick = Application.WorksheetFunction.Small(Range_1, i) A = Application.WorksheetFunction.Hlookup(Pick,Range_2 , x, False) A1 = A1 + A i = i +1 Loop Until i = Z An alternative that comes to my mind is to get the address of the cell that contains the smallest i value in the array Range_1 and then use offset to get correspodning values from Range_2. However, I cant get the address of that cell. A close approach is as follows but it distrurbs the over all logic of my model: Do If Worksheets(2).Range("F71").Offset(0, Count).Value = Pick Then Addr = (Worksheets(2).Range("F71").Offset(0, Count).Address) A = Worksheets(2).Range(Addr).Offset(5, 0).Value A1= A1=A Count = Count +1 Loop Can any one help me with 1. To obtain the address of the cell with smallest i vale in Range_1 and repeating the process. Or 2. Lookup Returning multiple values in VBA Or anyother suitable approach which solves my problem. Many Thanks Mallick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup to return multiple values | Excel Worksheet Functions | |||
LOOKUP TO RETURN MULTIPLE VALUES | Excel Discussion (Misc queries) | |||
Lookup and Return Multiple Values | Excel Worksheet Functions | |||
lookup and return multiple values | Excel Worksheet Functions | |||
how to lookup a value and return multiple corresponding values | Excel Worksheet Functions |