# 17. Exercise - MATCH and INDEX Functions

We return to the previous exercise using the MATCH and INDEX functions.

## Prerequisites

You should read the Excel Functions course up to the chapter on MATCH and INDEX 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 functions MATCH and INDEX.

You can download the file for this exercise here.

## Indications Exercise - MATCH and INDEX Functions

*Solution :*

Formula in E2 of sheet 2 :

**=INDEX(Feuil1!$D$2:$D$1475;EQUIV(A2;Feuil1!$A$2:$A$1475;0))**

*Explications :*

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.

The row in sheet 1 that corresponds to the current row in sheet 2 is found by calling the function MATCH with :

- as 1
^{st}argument the Name which is the value of cell A2 in this sheet - as 2
^{nd}argument, the cell range A2:A1475 of sheet 1 - in the 3
^{rd}argument, the value 0 for an exact match between the searched value and the list given in the 2^{nd}argument.

To extract from the row found the original department stored in column D, I used the INDEX function:

- in the 1
^{st}argument, the range of cells in column D of sheet 1, i.e. D2:D1475 - as 2
^{nd}argument, the number of the row returned by the function MATCH.