The VLOOKUP which stands for Vertical Lookup is one of the most frequently used advanced functions in Excel. The VLOOKUP is useful in updating a table with data from another table.

Let's say you were given two sheets; the first is the Master Udemy Course table which contains all the information about all Udemy courses and the second table contains just the Udemy Course IDs. You were asked to populate this second table with the title of the courses and the corresponding web address of each course ID. Remember, this second table only has the Course IDs.

The VLOOKUP is perfect for scenarios like this. It takes a value from the table you want to populate and searches for that value in the master table, in this case, the Master Udemy Course table. When it finds the value, it then searches for the column indicated in the VLOOKUP formula and retrieves it.

The VLOOKUP has 4 parameters that should be filled in to return the value you want

• Lookup Value: This is the value that serves as a reference point between the two tables. In this case the lookup value is A2 which is the course_id we want to search for in the Master Udemy Course List

• Table Array: The Table Array is the name and the extent of the table. In this case, the Table Array is 'Cleaning and Collecting Data - '!$A$1:$O$3677. The '$' sign there means the value is absolute and so it doesn't change even when we drag it down along the rows to auto-complete

• Col_index_num: This is the number of the column you want to return. In this case, the course_title occupies the 2nd column in the Master Udemy Course Table. So we filled in the number 2. If the course title occupied the 5th column, we would have filled in 5 as the col_index_num.

• Range Lookup: This parameter takes a boolean value; True or False. True indicates that you want VLOOKUP to search for the nearest match in ascending order. This is useful in cases where you’re not very sure of the value you’re looking for. False indicates that you want VLOOKUP to retrieve the exact match. If it doesn’t find the exact match, it returns #N/A

The VLOOKUP is a powerful tool, however, it has some limitations:

The Lookup value must always be the first column in the corresponding Table Array.

The VLOOKUP reads only to the right and cannot read to the left so it will be impossible to retrieve columns on the left side of the Lookup Value column. To retrieve values irrespective of the lookup value position, we will have to combine the Index and Match Functions. We will look at this in another article.

The VLOOKUP is a function that helps populate a table with values from another table as long as there is an identical column in both tables.

I hope you enjoyed this one.

Till we meet again,

Koms