How to Use VLOOKUP Formula for Auto-filling table in Excel
Most Excel tables are set up so that the most important and unique data for each row is located in the far-left column. Typically, the left side contains names, and the right side contains metrics. Excel offers many different value lookup functions, but the VLOOKUP function is particularly well-suited for this type of task.
How to Use the VLOOKUP Formula in Excel
Below is an employee list. Let’s say we need to create a simple payment form that can be automatically filled with data selected from this table. All values will be selected from the table based on the specified employee ID.
The user will have the option to enter the employee ID in cell B14. Based on that ID, all other data—first name, last name, address, etc.—will be automatically filled. The formulas used in the form are shown in the image:
=VLOOKUP($B$14,$A$2:$H$11,2,FALSE)
All the formulas that read data from the employee table use the VLOOKUP function. It has four arguments:
- Lookup value.
- Table array.
- Column index number.
- Range lookup (optional) – determines exact (FALSE) or approximate (TRUE) match for the lookup value.
If the VLOOKUP function finds the lookup value, it returns the current value of the cell, but from the column number specified in its third argument. In this example, the third argument is 2, meaning the function will return the employee’s first and last names from the second column of the table.
The last argument for each VLOOKUP function in this example formula is set to 0 (or FALSE). As mentioned earlier, if this fourth argument is set to FALSE, the VLOOKUP function will only return a result for an exact match of the lookup value. Otherwise, it will return the #N/A! error.
In this example, 5 formulas are used for auto-filling data, each using the VLOOKUP function with slight differences. The formulas for looking up the address:
=VLOOKUP($B$14,$A$2:$H$11,3,FALSE)
and insurance:
=VLOOKUP($B$14,$A$2:$H$11,7,FALSE)/VLOOKUP($B$14,$A$2:$H$11,4,FALSE)
are similar to the formulas that look up the employee’s first and last names, but they read data from other columns in the table.
The formula for calculating the payment uses two VLOOKUP functions, as the result of one formula is divided by the result of the second. The annual bonus is retrieved from the fifth column of the table and divided by the payment frequency from the fourth column. This calculates the gross one-time payment:
=VLOOKUP($B$14,$A$2:$H$11,5,FALSE)/VLOOKUP($B$14,$A$2:$H$11,4,FALSE)
The formula for calculating the pension fund contribution retrieves the percentage from the eighth and last column of the table and multiplies it by the total payment amount:
=C17*VLOOKUP($B$14,$A$2:$H$11,8,FALSE)
Download the example of how to use the VLOOKUP formula for auto-filling in Excel
The fifth formula calculates the tax amount by subtracting the insurance and pension fund contribution from the total payment and multiplying the result by the tax rate percentage found in the sixth column of the table using the VLOOKUP function.
The total amount of deductions in cell G20 is calculated with the formula: =SUM(G17:G19).
The net payment in cell G14 is calculated by subtracting the total deductions from the gross payment: =C17-G20.
Of course, in reality, more complex calculations are often used for payment calculations than in the example above. But if you master the principle of building formulas based on the VLOOKUP function, you can confidently tackle even the most complex final calculation models with automatic form field filling.
The key purpose of many Excel formulas is to look up data in a table. Excel offers many functions that make it significantly easier to search for data in vertical or horizontal tables. From left to right and vice versa, for example, using a formula with two functions INDEX and MATCH or a single LOOKUP function. By combining some lookup functions with others, you can create formulas that will find data even after the table’s position has been changed or transposed.