You can use Range("A2").Value to dynamically update lookup value from range A2. Next we have used the loginID variable to store lookup value. If you are not sure, which kind of value will be returned by the worksheet function, use variant type variables. type of value, use that kind of variable to store the result. If your worksheet function is expected to return number, date, range, etc. I have used string type variables because I am sure that the result returned by VLOOKUP will be a string value. Now let's examine the code.įirst we have declared two variables of string type to store the result returned by the VLOOKUP function. You can see how quickly the VBA prints the result into a message box. When you run this code, you will get this result. MsgBox ("Name: " & name & vbLf & "City: " & city) 'Using VLOOKUP function to get city of given id in tableĬity = (loginID, Range("A1:K26"), 4, 0) 'Using VLOOKUP function to get name of given id in table Press ALT+F11 to open VBE and insert a module. I need to show the Name and City of given Login Id in a message box using VBA. To demonstrate how you can use a VLOOKUP function in VBA, here I have sample data. So let's use some worksheet functions to understand it better. For example, if you want to pass a range A1:A10, you will have to pass it as a range object like Range("A1:A10"). But you will have to pass variables in VBA understandable format. Once you have chosen the function name, it will ask for the variables, like any function on excel. VBA's intellisense will show the name of the functions available to use. And start writing the name of the function. In any sub, write Application.WorksheetFunction. And using dot operator, you can access them all. Almost all the worksheet functions are listed into Application.WorksheetFunction class. To access the worksheet function we use an Application class. How do we do that? In this article, we will explore exactly that. But what if we want to use VLOOKUP in a VBA.
We all know how to use VBA functions in VBA. On the other hand the user defined functions and functions specific to VBA like MsgBox or InputBox are VBA functions.
You can't alter or see the code behind these functions in VBA. Generally, the functions that are predefined in Excel and ready to use on a worksheet are worksheet functions. Thanks to for the sample.The functions like VLOOKUP, COUNTIF, SUMIF are called worksheet functions. It is a powerful tool that makes life much easier when dealing with larger spreadsheets.
There you have it, the anatomy of VLOOKUP in Excel 2016. Type a search criteria into the box at F2 you created in step 2 and see the return in the box below at F3.Tell the spreadsheet whether you need an exact match or approximate by adding TRUE or FALSE.In the example, that is salary so we use column 3. Tell Excel what value you’re looking for.Add the search area of the spreadsheet.For example, if you’re sending the result to F2, your lookup should look like: ‘ =VLOOKUP($F$2’. Add where you want the data displayed.Type ‘ =VLOOKUP(‘ into F3 where you would like your data displayed.Add a box above it in which to put your search criteria.Put a box around it or otherwise mark it out so you can see clearly what is returned. Open your spreadsheet and find somewhere to place the results.The formula we are going to end up with is ‘ =VLOOKUP($F$2,$A$2:$C$55,3,FALSE)’