Lookup Return Multiple Values in VBA

[Mallick]

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

 

[Jacob Skaria]

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

 

[Jacob Skaria]

I have only modified your code...Please check out the undeclared variables....

If this post helps click Yes

 

[Mallick]

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]

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