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 1st 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 1st argument the Name which is the value of cell A2 in this sheet
- as 2nd argument, the cell range A2:A1475 of sheet 1
- in the 3rd argument, the value 0 for an exact match between the searched value and the list given in the 2nd argument.
To extract from the row found the original department stored in column D, I used the INDEX function:
- in the 1st argument, the range of cells in column D of sheet 1, i.e. D2:D1475
- as 2nd argument, the number of the row returned by the function MATCH.