How to Use VBA RIGHT Function with VLOOKUP in Spreadsheets
Question details
The user wants to perform a VLOOKUP in a VBA macro using only the last three characters of a cell's value as the lookup criterion.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Automating data retrieval in a spreadsheet where the lookup key is embedded at the end of a string (e.g., extracting 'ACV' from 'BGR-ACV').
- Observed behavior
- The user needs a working VBA macro script that successfully extracts the rightmost characters and passes them into the WorksheetFunction.VLookup method.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled the Developer tab to access the VBA Editor.
Combine VBA RIGHT Function with Application.WorksheetFunction.VLookup
Use the Right() function directly inside the VLOOKUP method to extract the required characters dynamically during a loop.
By nesting the VBA Right function inside the WorksheetFunction.VLookup method, you can dynamically extract the final characters of a string and use them as your lookup value. This is especially useful for processing standardized codes to extract the relevant portion before querying another worksheet.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu and select 'Module' to create a blank script window.
Copy and paste the following code into the module window: 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
Modify the worksheet names (such as '72 Hr' and '3 LTR'), column indexes, and the number '3' in the Right function to match the structure of your specific dataset.
Press F5 or click the 'Run' button in the toolbar to execute the macro. The script will populate the destination cells with the extracted VLOOKUP results.
Automate Your Data with WPS Spreadsheet
WPS Spreadsheet provides powerful data processing capabilities, including full support for VBA macros, VLOOKUP, and text manipulation functions. You can write and execute your VBA scripts seamlessly in a familiar interface.
- 1. Download and Install: Get WPS Office from the official website and open WPS Spreadsheet.
- 2. Open Macro-Enabled File: Open your existing .xlsm file or create a new workbook and save it as a Macro-Enabled Workbook.
- 3. Access Developer Tools: Go to the Developer tab and click on 'Macros' or 'VB Editor' to start writing your VLOOKUP scripts.
Frequently Asked Questions
Can I use the LEFT or MID functions with VLOOKUP in VBA?
Yes. Just like the RIGHT function, you can nest VBA's Left() or Mid() functions within Application.WorksheetFunction.VLookup to extract text from the beginning or middle of a cell's string before performing the lookup.
Why am I getting a 'Type Mismatch' error when running this macro?
This often happens if the RIGHT function extracts a text string, but your lookup table contains numbers formatted as values. You may need to wrap the RIGHT function in CInt() or CLng() to convert the extracted text to a number before passing it to VLOOKUP.
How do I make the VLOOKUP perform an exact match instead of an approximate match?
In the WorksheetFunction.VLookup parameters, change the final argument from '1' or 'True' to '0' or 'False'. This forces the function to look for an exact match in your lookup table.
Can I handle errors if the VLOOKUP doesn't find a match?
Yes, you can use 'On Error Resume Next' before the VLOOKUP line, or wrap the lookup in an error-handling block to prevent the macro from stopping completely when a lookup value is missing.
