PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

EXCEL FUNCTIONS-INDEX

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 MATCH 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.

PREREQUISITES TO LEARN MATCH

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

SYNTAX: INDEX

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 atleast 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.

 

GYANKOSH MULTI RANGE SELECTION
EXCEL:MATCH FUNCTION:ANIMATED EXAMPLE

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.

INDEX FUNCTION EXAMPLE
EXCEL:INDEX FUNCTION:SOLUTION EXAMPLE 1

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 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.
GYANKOSH SOLUTION INDEX FUNCTION
EXCEL:INDEX FUNCTION:EXAMPLE 2 SOLUTION

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

GYANKOSH INDEX FUNCTION CLARIFICATION
EXCEL:INDEX FUNCTION:ROW COLUMN NUMBER CLARIFICATION

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.

GYANKOSH ACTUAL IMPLEMENTATION OF CLARIFICATION
EXCEL:INDEX FUNCTION:ROW COLUMN NUMBER CLARIFICATION IMPLEMENTATION

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: