# VLOOKUP and right three letters

#### Livin404

##### Active Member
Greetings, I have a VBA that works great where if the three letter code "ACV" is provided it will search over 10K rows for the appropriate response. However, on occasion I may be given a 7 digit character "BGR-ACV". I am hoping someone can let me know how I can insert a rule where it will search only the last three characters to the right. My current VBA is:

VBA Code:
``````Private Sub VLOOK_UP()
Dim i As Integer
For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "\$")(4)
Worksheets("72 Hr").Cells(i, 12).Value = _
Application.WorksheetFunction.VLookup(Worksheets("72 Hr").Cells(i, 8).Value, _
Worksheets("3 LTR").Range("A:B"), 2, 1)
Next i
End Sub``````

I think need something like
Excel Formula:
``VLOOKUP(RIGHT(A2,3)``
but I don't know how or where to slip something like that in. Thank you,

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Joe4

Is that what you are looking for?
VBA Code:
``````Private Sub VLOOK_UP()
Dim i As Integer
Dim lkup as String
For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "\$")(4)
lkup = Right(Worksheets("72 Hr").Cells(i, 8).Value, 3)
Worksheets("72 Hr").Cells(i, 12).Value = _
Application.WorksheetFunction.VLookup(lkup, _
Worksheets("3 LTR").Range("A:B"), 2, 1)
Next i
End Sub``````

#### Livin404

##### Active Member
HI, I just sorted it out about 20 minutes ago and it works great. Here is the Code and I think it is just like yours, or very similar. I was about to post this on here, to let people know. I'll give you the check mark for I'm sure it is spot on. You have always been very helpful. Thank you,

VBA Code:
``````Private Sub VLOOK_UP()
Dim i As Integer
For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "\$")(4)
Worksheets("72 Hr").Cells(i, 12).Value = _
Application.WorksheetFunction.VLookup(Right(Worksheets("72 Hr").Cells(i, 8).Value, 3), _
Worksheets("3 LTR").Range("A:B"), 2, 1)
Next i
End Sub``````

#### Joe4

You are welcome.
Yes, the only real difference between our code is that I did the function on the lookup variable separately, so as not to overcomplicate the formula.
Either way works fine, neither one is more correct than the other.

Replies
2
Views
99
Replies
2
Views
193
Replies
7
Views
91
Replies
6
Views
145
Replies
22
Views
457

1,127,528
Messages
5,625,339
Members
416,096
Latest member
forevans

### 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