Excel VBA - Vlookup with Choose and column concatenation.

[systemlordanubis]

Hi All,

In one of my sheets I have the following formula in a field:

{VLOOKUP(E5,CHOOSE({1,2}, 'Sheet1'!B3:B100 & ":" & 'Sheet1'!C3:C100, 'Sheet1'!G3:G100), 2, 0)}

I'm needing to convert this formula into pure VBA but I'm having an issue with this part:

'Sheet1'!B3:B100 & ":" & 'Sheet1'!C3:C100


This is what I have so far for the VBA equivalent:

Application.WorksheetFunction.VLookup("ValueOfE5", Application.WorksheetFunction.Choose(Array(1, 2), TheWorksheet.Range("B3:B100") & ":" & TheWorksheet.Range("C3:C100"), TheWorksheet.Range("G3:G100")), 2, False)


Is there a way I can concatenate these two columns together (B & C) with the intermediary 'colon' so that I can match the value.

The sample data of each of the fields is:

E5 = "Apple:123"
Sheet1!B = "Apple"
Sheet1!C = "123"
Sheet1!G = Value I require

Any suggestions would be most welcome.

Thanks
Anubis.

 

[JackDanIce]

Where is the output going to?

This UDF can be called from your procedures, but may need modifying, however, try:

Public Function CustomLOOKUP(ByRef InputRng As Range) As Variant    
    Dim arr()   As Variant
    Dim dic     As Object
    Dim x       As Long
    Dim temp    As Variant
    
    Set dic = CreateObject("Scripting.Dictionary")
            
    arr = Sheets("Sheet1").Range("B3:G100").Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        temp = arr(x, 1) & ":" & arr(x, 2)
        dic(temp) = arr(x, UBound(arr, 2))
    Next x
    
      CustomLOOKUP= dic(InputRng.Value)
    
    Erase arr
    Set dic = Nothing
    
End Function

Then in your main code you can use:

Range("H3").Value = CustomLOOKUP(Range("E5").Value)

Put the UDF in the same module as the main code, just under it for ease of reference.