Quick-guide to VLOOKUP for Automation Experts

Last Updated on June 1, 2022 by Rakesh Gupta

How would you fill in data in the green cell in Table 2 using data from Table 1?

The Logic

Well, one simple approach is to use VLOOKUP. To do that let us first understand this function.

VLOOKUP or ‘Vertical Lookup’ helps in ‘looking up in vertical direction’ (hence the name VLOOKUP) a value in a data array or data organized in rows and columns.

Once it locates the ‘look-up-value’, corresponding column values can be pulled out.

So, if we wish to fill the green cells in Table 2 with a name (say Lee Nazal), we start by asking VLOOKUP to look for the name ‘Lee Nazal’ in Table 1 (which is row 9). Then VLOOKUP is to give us the corresponding value in the columns “Department” (which is the second column in Table 1) and “Salary” (which is column 3).

This is essentially how the logic of VLOOKUP works.

Formula

Now we know the logic, here is how the formula looks like (also called its syntax):

VLOOKUP is made up of 4 important fields, namely:

1. lookup_value: The value you want to look up in a data array and find its corresponding column values (i.e. cell E3).
   
2. table_array: This is the table where you want to pull out the data in Table 1 (i.e. $A$2:$C$56) – note the $ sign.
3. col_index_num: This is important! So, in the example, above we are looking for a Table 2 field – ‘Name’ in Table 1 and pulling out the corresponding Department and Salary. So, let’s see what the column numbers for ‘Department’ and ‘Salary’ in Table 1 are compared to the look-up column (i.e. ‘Name’). Here, it’s easy to see that Department is ‘second’ and Salary is ‘third’ column. Note ‘Name’ is the first column. These relative positions (First=1, Second=2, Third=3) are the col_index_num!
4. range_lookup: This implies whether we want to find an exact(0) or approximate match (1) (for more information about the exact and approximate match in VLOOKUP, read here).

So, this is how the final formula looks:

The real power of VLOOKUP is unleashed when it’s used with ‘cell-referencing’ which is beyond the scope of this article. But to give you a taste test, by using correct referencing, VLOOKUP can easily be dragged around the green area above to populate the formula very quickly.

Here is a screenshot that demonstrates this:

As we can see by putting $ (E$3) we can easily drag the formula down as shown by the gray arrow.

Note this would not be possible if we use E3 instead of E$3.

Now if you have understood VLOOKUP, try filling up the salary column using the same approach using the attached exercise file (please link to the attached exercise file).

About the author

Spreadsheeto specializes in teaching you Excel as fast and fun as possible. Spreadsheeto offers tons of free resources on their blog as well as paid training. Did you like this article? Then head over and sign up for their free Excel training: http://spreadsheeto.com/free-excel-training/