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

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

## 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

### explanation -example 1

## EXAMPLE 2:INDEX FUNCTION IN EXCEL:REFERENCE FORM

### DATA SAMPLE

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

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

YOU MAY LIKE

- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.