16. Exercise - VLOOKUP and HLOOKUP functions

---

Prerequisites

You should read the Excel Functions course up to the chapter on VLOOKUP and HLOOKUP functions before starting this exercise.

Question

Consider the spreadsheet extract representing a company's staff list. In column D is recorded the department for each member at the time of recruitment.

On another sheet of the same workbook, the same list is recorded with the current department for each staff member in column D.

Write the formula in cell E2 to extract the initial department from column E of the 1^st sheet, using the Excel function VLOOKUP.

You can download the file for this exercise here.

---

Indications Exercise - VLOOKUP and HLOOKUP functions

---

Solution :

Formula in E2 of sheet 2 :

=VLOOKUP(A2,Sheet1!$A$2:$D$1475,4,FALSE)

Explanation :

The formula must

• search for the row in Sheet 1 that corresponds to the current row in Sheet 2
• extract from the row found the initial department stored in column D

The key value between the list in Sheet 1 and the list in Sheet 2 is the name of the staff.

We therefore called the function VLOOKUP with :

• in the 1^st argument, the name, i.e. the value of cell A2 of the present sheet
• in the 2^nd argument, the cell range A2:E1475 of sheet 1. In fact, the name given in the 1^st argument is searched in the 1^st column of this range, i.e. column A
• in the 3^rd argument, it is the value 4. When the name given in the 1^st argument is found in column A of the range A2:D1475, the value corresponding to the 4^th column is returned.
• in 4^th argument, it is the value FALSE for an exact match between the names written in the two lists.

---