Vlookup and now also Xlookup are must known for every analyst that even remotely works in excel. However, in order to use them in vba one needs to call the worksheetfunction objects as both vlookup and xlookup are not predefined syntaxes in vba already.
Let's say we have a table with the classic analyst phrase. "Please Fix Thx." and that we want to access one of these based on an input value in vba.
A = "Please"
B = "Fix"
C = "Thx."
Please find my script below for Vlookup:ing the 3rd word in "Please Fix Thx.".
Note that the below code would also work with xlookup as well as any other excel command.
Sub Vlook()
Range("A1").Activate
MyValue = Application.WorksheetFunction.VLookup(3, Range("A:B"), 2, False)
Debug.Print MyValue 'Printing out my result from the lookup.
End Sub
Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba
Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python
If you have found this article or website helpful. Please show your support by visiting the shop below.