How to use the Index Function
The Index Match function is an extremely handy function. It allows you, in effect to create a VLookup but the lookup column doesn't have to be in the left most column. However how does the Index function actually work.
This video will allow you to become familiar with the Index function. The Index function will look at an array or range of cells and bring back a value in the cell by typing in a number in the function arguments.
- Click in cell D2 enter in the formula:
The first part of the formula is the range and the second part of the formula is the row number. The result will be whatever is in that cell. In this case the answer is Vanilla.
- Click in cell D6 then select type =INDEX( then select cells A6:C6, type a comma then the number 3 so that the formula reads.
Note that Tutti-frutti is returned as a value. This is because we have only selected one row. That being the case the second arguement is now refering to the column number.
- If the range includes rows and columns you can type in an extra argument the last being the column number.
- Click in Cell D11 and type the following:
Note that the answer is Coconut because Coconut is the value in the second row down and the second column across.
In the formula the first number 2 is now the row number and the second number 2 is the column number. So for example if we typed =INDEX(A11:C12,3,1) the answer would be Bubble gum, as Bubble gum is in the third row down and the first column.
Now that you've got to grips with the INDEX function have a look at how you can use the INDEX function in conjunction with the MATCH Function for a powerful VLookup alternative. Have a look at our Excel Index Match Function video.