So, the formula =VLOOKUP(RC, R3C8:R13C9, 2, False) means =VLOOKUP(,, , ). In this code, we have applied the VLOOKUP in R1C1 form.
#How do you use vlookup in excel 2016 code
Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC, R3C8:R13C9, 2, False)"Įxplanation: This code is very similar to the one that we have discussed in Example 3, the only difference between these formulas is that here we are copying the VLookUp formula directly in the cells. Redo example 3 but this time paste the whole VLookUp formula instead of pasting only the result. MsgBox "You have entered an invalid value."Įxplanation: In this example, we have asked the user to enter the Employee Id and then we have used multiple VLookUp Statements and concatenated their outputs to show all the details in a single message box. MsgBox "Employee Details : " & vbNewLine & Det In this example we will try to write a code that displays all the details of an employee from the Employee table (as shown below) when its Employee ID is entered.īelow is the code that can accomplish this:Į_id = InputBox( "Enter the Employee ID :")ĭet = "Employee ID : " & (E_id, Sheet1.Range( "A3:E13"), 1, False)ĭet = Det & vbNewLine & "Employee Name : " & (E_id, Sheet1.Range( "A3:E13"), 2, False)ĭet = Det & vbNewLine & "Employee SSN : " & (E_id, Sheet1.Range( "A3:E13"), 3, False)ĭet = Det & vbNewLine & "Monthly Salary : " & (E_id, Sheet1.Range( "A3:E13"), 4, False)ĭet = Det & vbNewLine & "Department : " & (E_id, Sheet1.Range( "A3:E13"), 5, False) Please note that in this code we have just pasted the result of the VLOOKUP formula, and not the VLookUp formula itself (Refer Example 5). Sheet1.Cells(Dept_Row, Dept_Clm) = (cl, Table2, 2, False)Įxplanation: This code takes each ‘ lookup_value’ from the Employee ID field (one at a time), looks up its corresponding Department, and then populates the corresponding department value at the appropriate place. Table2 = Sheet1.Range( "H3:I13") ' Range of Employee Table 1ĭept_Row = Sheet1.Range( "E3").Row ' Change E3 with the cell from where you need to start populating the Department
![how do you use vlookup in excel 2016 how do you use vlookup in excel 2016](https://i.ytimg.com/vi/aJXgqNhRWMM/maxresdefault.jpg)
Table1 = Sheet1.Range( "A3:A13") ' Employee_ID Column from Employee table So, in this case, we will have to apply the VLookUp based on the Employee ID. In this example we will try to write a code that adds the Department field from the Employee Table 1 to our old Employee Table.Īs you can see that in both these tables there is only one common column i.e.
![how do you use vlookup in excel 2016 how do you use vlookup in excel 2016](https://www.teachexcel.com/images/uploads/45accb280c25d2bf87ad5da9aab13195.png)
However, if the employee name is not present in the table then VLOOKUP throws a “1004 Error”.Īnd, we have created an error handler to catch such cases for conveying the user that entered employee name doesn’t exist. If the Employee name entered by the user is found, then VLookUp returns its corresponding salary. MsgBox "Employee Not Present in the table."Įxplanation: In this code, we are accepting the user input using an InputBox function. E_name = InputBox( "Enter the Employee Name :")