Index()
Index() works on a specified range of cells returns the contents of a particular cell a specified number of rows down and a specified number of columns across. The formula is structured thus:
=Index(range of cells, Row number, column number) NB in Excel speak range of cell is referred to an ‘Array’.
In the following example
- the range of cells is the pink area cells C6 to K19
- we want the value 5 rows down and 6 columns across
- So the formula we write is =index($c$6:$k$19,5,6)
- We get the result 130 which is in cell H10
This lacks flexibility in that it only looks at a single cell within a single range of cells. Fortunately Excel allows us to replace each part (argument) of the formula with another formula which will give us the flexibility that is often required. We can replace the row and/or column numbers with a match formula (see below) and the range of cells with an indirect formula.
Match()
Match() examines a list of data and tells you how far down that list a particular item is. The formula is written as =Match (lookup value, range of cells, match type) where
- Lookup value is the item you are trying to find in your list. E.g. ‘Elena’ in the example on the right
- Range of cells is the list of data e.g. cells B5 to B18 in the example on the right
- Match type
- 0 tells the formula you want an exact match to the lookup value. This is by far and away the most common use.
- 1 finds the largest value that is less than or equal to the lookup value
- 1 finds the smallest value that is greater than or equal to the lookup value
Some points to note when using match
- The formula works equally well with a vertical or horizontal list
- The formula only works on a single column (vertical list) or single row (horizontal list)
- The list must be continuous i.e. there must not be any gaps
- If the lookup value does not exist then the #N/A error is returned
- Match will work on a full column or row. In this case it would return the actual row number of the lookup value.
- On its own match is of limited value but when nested into another formula, such as INDEX(), it adds flexibility to that formula. See below for a more detailed explanation
- Match() can usefully be used to check for completeness of data. For example you can compare a current period data to the previous period data to check that no new items have occurred. It has an advantage over the countif() method of doing this as it tells where the new item is in your current period list
Index() with Match()
Match() and Index() are explained separately above. The combination of the two formulas in a single formula gives it flexibility – especially if the formula is linked to inputs that can be varied.
- In the example below Match() is nested into the index formula in place of the row number.
- It uses the input in cell C2 (which is selected via a data validation list).
- Therefore a different result will be produced each time a different selection is made in cell C2.
Indirect()
The Indirect() function converts a text into a cell reference.
- The text can be directly entered into the formula or held in another cell.
- It can be a single cell reference (e.g. D7) or a range reference e.g. D7:Q26
- The range can be fixed with the $ symbol e.g. $D$7:$Q$26
- The formula also recognises Range names (Like all other formulas) e.g. if the range D7:Q26 is given the Range name ‘Data’ then you can write the formula =INDIRECT(Data)
- The formula becomes particularly useful when it is nested (i.e. embedded) within other formulas such as index (see below).
Index() with Indirect()
The indirect formula can replace the ‘Range of cells’ argument within the Index() formula (see index() section for more information). Thus it becomes =Index(Indirect(“ref”), Row number, Column number).
In the example below
- The aim is to provide a summary table (in cells B3:N6) that will update automatically as we change the name of the person we are interested in.
- A name can be chosen in Cell B2 using the data validation set up in that cell
- Two range names have been defined:
- ‘Actuals’ for the range C13:N17
- ‘Budget’ for the range C20:N24
- The titles of the first two rows match the range names defined.
- The Indirect() enables the same formula to be used for both rows of data in the summary table by using the row heading to refer to the data to interrogate i.e. ‘actuals’ or ‘budget’
- The Index() formula also utilises the match() function for both the row and the column arguments.
- Thus the full formula becomes:
- =INDEX(INDIRECT($B4),MATCH($B$2,$B$13:$B$17,0),MATCH(C$3,$C$11:$N$11,0))
- The appropriate use of the $ sign enables this formula to be copied to all cells in the range C4:N5.
Excel Monkey 