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.

Excel 2016 Exercise - VLOOKUP and HLOOKUP functions

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

Excel 2016 Exercise - VLOOKUP and HLOOKUP functions

Write the formula in cell E2 to extract the initial department from column E of the 1st 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 1st argument, the name, i.e. the value of cell A2 of the present sheet
  • in the 2nd argument, the cell range A2:E1475 of sheet 1. In fact, the name given in the 1st argument is searched in the 1st column of this range, i.e. column A
  • in the 3rd argument, it is the value 4. When the name given in the 1st argument is found in column A of the range A2:D1475, the value corresponding to the 4th column is returned.
  • in 4th argument, it is the value FALSE for an exact match between the names written in the two lists.

<< 15. Exercise - COUNTBLANK Function

17. Exercise - MATCH and INDEX Functions >>