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