Table of Contents
- INTRODUCTION
- PURPOSE OF INDEX FUNCTION IN EXCEL
- PREREQUISITES TO LEARN INDEX
- SYNTAX: INDEX FUNCTION
- MULTIPLE SEARCH RANGES AND AREA NUM
- EXAMPLE 1:INDEX FUNCTION IN EXCEL:ARRAY FORM
- EXAMPLE 2:INDEX FUNCTION IN EXCEL:REFERENCE FORM
- CONFUSION CLARIFICATIONS
INTRODUCTION
INDEX FUNCTION comes under the LOOKUP AND REFERENCE group of functions in Excel.
INDEX FUNCTION returns the value of a cell or array in a specified relative location given by row no. and column no. in a selected range.
Relative position is the position with respect to the select LOOKUP_RANGE.
e.g.
We have five COLORS and five CODES and we need to find out a value on a particular position.So we can use INDEX function for this scenario and it’ll find out the item to be searched at a particular position.Suppose there is a table of 4 columns and 4 rows. If we want to search that what element is present at row no. 3 and column no. 4, we’ll take help of INDEX FUNCTION.
PURPOSE OF INDEX FUNCTION IN EXCEL
INDEX FUNCTION LOOKS UP A VALUE AT A SPECIFIC LOCATION WHICH IS MENTIONED BY RELATIVE ROW NO. AND RELATIVE COLUMN NO. IN A SELECTED LOOKUP TABLE OR RANGE.
Index function in conjunction with the other functions like MATCH FUNCTION creates a very powerful duo for looking up the values in the given data.
In addition to this index function is used at a number of places for searching out the practical solutions.
PREREQUISITES TO LEARN INDEX
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Easier to understand if user already knows how to use VLOOKUP.
- Basic understanding of rows and columns in Excel.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel?
SYNTAX: INDEX FUNCTION
The INDEX FUNCTION is used in two forms
1. Array Form
2. Reference Form
The syntax ( the way how formula is phrased for excel) of INDEX FUNCTION in ARRAY FORM is
=INDEX(ARRAY IN WHICH THE VALUE WILL BE SEARCHED FOR , RELATIVE ROW NUMBER , RELATIVE COLUMN NUMBER )
ARRAY IN WHICH THE VALUE WILL BE SEARCHED FOR The table or range selected for the look up. It can be a single row or single column too.
RELATIVE ROW NUMBER ROW , FROM WHICH THE DATA WILL BE PICKED UP
RELATIVE COLUMN NUMBER COLUMN NUMBER, FROM WHICH THE DATA WILL BE PICKED UP
NOTE: Either of the ROW NO. OR COLUMN NO. is optional but at least one should be present.
The syntax ( the way how formula is phrased for excel) of INDEX FUNCTION in REFERENCE FORM is
=INDEX(REFERENCE/REFERENCES IN WHICH THE VALUE WILL BE SEARCHED FOR , RELATIVE ROW NUMBER , RELATIVE COLUMN NUMBER, AREA NUMBER )
REFERENCE/REFERENCES IN WHICH THE VALUE WILL BE SEARCHED FOR The table or range selected for the look up. It can be a single row or single column too.
RELATIVE ROW NUMBER ROW , FROM WHICH THE DATA WILL BE PICKED UP
RELATIVE COLUMN NUMBER COLUMN NUMBER, FROM WHICH THE DATA WILL BE PICKED UP.
AREA NUMBER This is the number of the Reference from which the result will be given.
NOTE: Either of the ROW NO. OR COLUMN NO. is optional but at least one should be present.
MULTIPLE SEARCH RANGES AND AREA NUM
If you noticed in the previous section, specifically the formula for INDEX FUNCTION with the reference mode, you might have got confused about the multiple ranges and area num. It’ll be cleared with the help of this picture.
INDEX function allows to list more than a single range suppose A, B, C and D.
Now look at the picture below.
Table A is the range given and it’ll correspond to the AREA_NUM as 1.
Table B is the range given and it’ll correspond to the AREA_NUM as 2.
Table C is the range given and it’ll correspond to the AREA_NUM as 3.
Table D is the range given and it’ll correspond to the AREA_NUM as 4.
ROW NUM , and COLUMN number won’t change. They’ll be same.
So the clarification means that we can easily find a particular index in four or more different areas.
EXAMPLE 1:INDEX FUNCTION IN EXCEL:ARRAY FORM
DATA SAMPLE
We’ll consider only Table A for this example.
Table A have the marks of 8 students with STUDENT IDs in different subjects. We’ll find the value at row no. 2 and column no. 3 in the table.
STEPS TO SOLVE
1. Enter the following formula in any cell , where you want the result as=INDEX(E7:I14,2,3)
2. The result will appear as 89
EXPLANATION : EXAMPLE 1
The formula used for the solution is=INDEX(E7:I14,2,3)
The first argument E7:I14 is the table which we’ll consider.
Second argument is the row number which we want.
And third argument is the column number which we want.
As its clear from visual inspection the item at index 2,3 is 89 so the formula gave us the correct result.
KINDLY DON’T FORGET TO GO THROUGH THE CLARIFICATION SECTION AT THE END.
EXAMPLE 2:INDEX FUNCTION IN EXCEL:REFERENCE FORM
DATA SAMPLE
In this example we’ll make use of 4 tables. The data consist of the marks of different students in different subjects.We’ll find out the element at 4,4 position of all the tables.We’ll find out the said element in all the tables one by one.
STEPS TO SOLVE [EXAMPLE 2]
1.Put the following formula in the cell where you want the result
=INDEX((B7:F14,H7:L14,B19:F26,H19:L26),4,4,1)
Similarly put the following formulas for table b , c and d.
formula for table b
=INDEX((B7:F14,H7:L14,B19:F26,H19:L26),4,4,2)
formula for table c
=INDEX((B7:F14,H7:L14,B19:F26,H19:L26),4,4,3)
formula for table d
=INDEX((B7:F14,H7:L14,B19:F26,H19:L26),4,4,4)
EXPLANATION [EXAMPLE 2]
The formula used for the first table is
=INDEX((B7:F14,H7:L14,B19:F26,H19:L26),4,4,1)
First argument is the list of all the ranges of all the four tables. These are put inside the parenthesis [“()”].
The ranges or lookup ranges can be 1,2 or more as per requirement.
Second argument is similar to INDEX FUNCTION -ARRAY FORM i.e. row number [optional if col number is present]
Third argument is again similar to INDEX FUNCTION-ARRAY FORM i.e. column number.[optional if row number is present]
*either of the row number or column number should be there. If any one is present , the other becomes optional.
FOURTH and final argument is the area number which tells which range is to be looked up. i.e. if first range, then 1, if second range, then 2 and so on. Other formulas can be inspected on the same lines.
Similarly put the following formulas for table b , c and d.
formula for table b
=INDEX((B7:F14,H7:L14,B19:F26,H19:L26),4,4,2)
formula for table c
=INDEX((B7:F14,H7:L14,B19:F26,H19:L26),4,4,3)
formula for table d
=INDEX((B7:F14,H7:L14,B19:F26,H19:L26),4,4,4)
DO YOU KNOW
THE ROW AND COLUMN NUMBERS ARE RELATIVE TO THE SELECTED RANGE. IT HAS NOTHING TO DO WITH THE ABSOLUTE ROW AND COLUMN INDEX.
CONFUSION CLARIFICATIONS
THE RELATIVE ROW AND COLUMN NUMBER
A cell is filled with YELLOW COLOR which we want to find. Now let us see in how many ways we can get this value.
The row and column number of the INDEX FUNCTION is relative to the RANGE SELECTED.
If we select Table 1, the value to be found will be at the row no. 4 and column no. 3.
If we select Table 2, the value to be found will be at the row no. 4 and column no. 3 again.
If we select Table 3, the value to be found will be at the row no. 3 and column no. 2.
So all these three different notations result in the same result. So always take care of the range selected and row and column no. selected.
