Vlookup with Choose

[ashish mehra]

Hi,

I am using code to get second instance of lookup value.
=vlookupAM(A2,$A$2:$B$20,2)

Function vlookupAM(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
Dim Result As String
For i = 1 To LookupRange.Columns(1).Cells.Count
    If LookupRange.Cells(i, 1) = Lookupvalue Then
    Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & ","
    End If
Next i
vlookupAM = Left(Result, Len(Result) - 1)
End Function

The requirement is:

formula: =vlookupAM(B2,CHOOSE({1,2},$B$2:$B$20,$A$2:$A$20),2)

Regards,
AM

 

[NARAYANK991]

Hi ,

I doubt that you should be coding this formula.

Normally code can be written to do what a function does , because a function follows a pre-defined syntax , and a function in VBA can have exactly the same number of parameters , with the same types , and in the same order as the worksheet function. You can always add functionality to a VBA function if it serves a purpose , and for this reason , a VBA function can have more parameters than the worksheet function it implements.

Here , you are trying to code a formula , and one which has a nested function.

The parameters in the VBA function are only 3 , whereas the worksheet function has 4.

The second parameter in the worksheet function is a function in itself , whereas the second parameter in the VBA function is declared as a range.

As far as range referencing in VBA is concerned , $A$2:$B$20 is identical to $B$2:$A$20 , and hence we cannot have the range as a single range.

If we need to create a virtual range or an array , then we need to write the code to implement the CHOOSE function , and the code for the VLOOKUP function will have to detect that the second parameter is a virtual range which makes use of the CHOOSE function. All this is bound to increase the complexity of the code.

I am at a loss to know how this nested formula can be implemented in VBA in a simple , transparent manner.

Narayan

 

[shrivallabha]

Maybe you should write IndexMatchAM

Formula will become:
=IndexMatchAM(B2,$B$2:$B$20,$A$2:$A$20)

Function IndexMatchAM(Lookupvalue As String, LookupRange As Range, OutPutRange As Range)
Dim i As Long
Dim Result As String
For i = 1 To LookupRange.Columns(1).Cells.Count
    If LookupRange.Cells(i, 1) = Lookupvalue Then
    Result = Result & " " & OutPutRange.Cells(i, 1) & ","
    End If
Next i
IndexMatchAM = Left(Result, Len(Result) - 1)
End Function