# Excel VBA - Vlookup with Choose and column concatenation.

#### systemlordanubis

##### New Member
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.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### JackDanIce

##### Well-known Member
Where is the output going to?

This UDF can be called from your procedures, but may need modifying, however, try:
Rich (BB code):
``````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:
Rich (BB code):
``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.

Last edited:

Replies
1
Views
357
Replies
3
Views
349
Replies
0
Views
307
Replies
8
Views
370
Replies
2
Views
99

1,191,122
Messages
5,984,773
Members
439,910
Latest member
Flyingjoblo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back