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

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