VLOOKUP and right three letters

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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,
 

Some videos you may like

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,649
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,649
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top