Excel INDIRECT Function

The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.

INDIRECT Function Arguments

The INDIRECT function has two arguments:

1. ref_text: A cell reference or text string (or both), that create the range reference. The referenced range can be a cell, a range of cells, or a named range.
2. a1: TRUE or FALSE. Does the reference use A1 reference style? If this argument is TRUE, or omitted, the ref_text is A1 style. If the argument is FALSE, the ref_text is R1C1 style.

 

Note: If the INDIRECT formula refers to a different workbook, that workbook must be open, or the formula will return a #REF! error.

Lock a Cell Reference

If you create a simple link to a cell, e.g. =A4, then insert a row above row 4, your formula will automatically change to =A5. To prevent this change, you can use the INDIRECT function.

To demonstrate the difference, you'll create two formulas -- one that uses a simple link, and one that uses a text string with an INDIRECT function.

1. In cell A4, type a number (10 in this example).
2. In cell B2, type the following formula:
      =INDIRECT("A4")
3. Press the Enter key, and the formula will return the number in cell A4.
4. In cell C2, type the following formula:
      =A4
5. Press the Enter key, and the formula will return the number in cell A4. 
6. To insert a row, right-click on the Row 4 button, and from the pop-up menu, choose Insert.
7. As shown below, cell B2, which uses the INDIRECT function, now returns 0, because cell A4 is empty. The text string, "A4", in the INDIRECT formula did not change when the row was inserted.
8. Cell C2, which contains the link, still returns 10, because its formula has changed to =A5. 

 

Create a Reference from a Cell Value

To create the ref_text argument, you can also refer to a cell, and use its contents. This makes the formula more flexible, as its results will change, if the cell contents change.

In this example, you'll create formulas with the INDIRECT function, to compare the A1 and R1C1 reference styles.

1. In cells A1:A5, type a set of numbers, as shown below.

2. In cell C2, type:  A4
3. In cell C2, type:  R4C1
4. In cell D2, type the formula: =INDIRECT(C2)
5. Press the Enter key, and the formula returns the number in cell A4
6. In cell D3, type the formula: =INDIRECT(C3,FALSE)
7. The FALSE in the second argument indicates that the R1C1 reference style will be used.
8. Press the Enter key, and the formula returns the number in cell A4

Excel Index and Match Functions

MATCH Function

The MATCH function can find a value in a list, and return its position. For example, where is "Jacket" in the list below?

	A	B
1	Item
2	Sweater
3	Jacket
4	Pants
5	Jacket	=MATCH(A5,ItemList,0)

1. Select the cell in which you want the result
2. Type an equal sign, the MATCH function name, and an opening parenthesis:
         =MATCH(
3. Type the value to find, or click on the cell that contains the value -- cell A5 in this example
4. Type a comma, to separate the arguments
5. Select the range that contains the list -- a range named ItemList in this example
6. Type a comma, to separate the arguments
7. Type the number for the Match type you want to use -- 0 was used in this example, because an exact match is required.
8. Finish with a closing parenthesis. The completed formula is:
         =MATCH(A5,ItemList,0)
9. Press the Enter key to see the result.

The result will be like the below figure: 

 
 

INDEX Function

The INDEX function can return an item from a specific position in a specific column in a list. For example, what is the 3rd item in the 2nd column in the list below?

	A	B
1	Item	Price
2	Sweater	10
3	Jacket	30
4	Pants	25
5	=INDEX(A2:B4,3,2)

1. Select the cell in which you want the result
2. Type an equal sign, the INDEX function name, and an opening parenthesis:
         =INDEX(
3. Select the cells that contain the list -- cells A2:B4 in this example
4. Press the F4 key on the keyboard, to change the reference to an absolute reference: $A$2:$B$4. Then, if you copy the formula to another cell, it will continue to refer to the list of items.
5. Type a comma, to separate the arguments
6. Type the number of the item you want to return -- 3 in this example
7. Type a comma, to separate the arguments
8. Type the number of the column you want to return -- 2 in this example
9. Finish with a closing parenthesis. The completed formula is:
         =INDEX($A$2:$B$4,3,2)
10. Press the Enter key to see the result.

  You can see the output of the function in below figure.

 

INDEX / MATCH

One advantage of the INDEX / MATCH functions is that the lookup value can be in any column in the array, unlike the VLOOKUP function, in which the lookup value must be in the first column.

In this INDEX / MATCH example, the MATCH function will find the position of "Jacket" in column B, and the INDEX function will return the code from the same position in column A.

	A	B
1	Code	Item
2	SW001	Sweater
3	JK002	Jacket
4	PN001	Pants
5
6	Jacket	JK002

1. Set up the worksheet as shown at right
2. Enter the following formula in cell B6:
         =INDEX(A2:A4,MATCH(A6,B2:B4,0))
3. Press the Enter key to see the result.

The MATCH function, MATCH(A6,B2:B4,0), returns 2, which is the position of "Jacket" in the list.

Then, the INDEX function, INDEX(A2:A4,2), returns "JK002", which is the second item in the rangeA2:A4.

INDEX / MATCH -- Example 2

The MATCH function can be used to return values for both the row_num and column_num arguments in the INDEX function.

	A	B	C	D
1		Small	Med	Large
2	Sweater	10	12	15
3	Jacket	30	35	40
4	Pants	25	30	35
5
6	Size	Item	Price
7	Med	Pants	?

1. Set up the worksheet as shown above
2. Enter the following formula in cell C7:       =INDEX($B$2:$D$4,MATCH(B7,$A$2:$A$4,0),MATCH(A7,$B$1:$D$1,0))
3. Press the Enter key to see the result.

The first MATCH function, MATCH(B7,A2:A4,0), returns 3, which is the position of "Pants" in the Items list.

The second MATCH function, MATCH(A7,B1:D1,0), returns 2, which is the position of "Med" in the Size list.

Then, the INDEX function, INDEX(B2:D4,3,2), returns "30", which is the third item in the second column in the range B2:D4.

INDEX / MATCH -- Example 3

To make the previous example even more flexible, you can use the INDEX function within the MATCH function, to look for values in the first row or column of a named table

	A	B	C	D
1		Small	Med	Large
2	Sweater	10	12	15
3	Jacket	30	35	40
4	Pants	25	30	35
5
6	Size	Item	Price
7	Med	Pants	?

Set up the worksheet as shown above

1. The green cells are a range named Table). (Instructions on naming a range)
2. Enter the following formula in cell C7:     
   =INDEX(Table,MATCH(B7,INDEX(Table,,1),0),MATCH(A7,INDEX(Table,1,),0))
3. Press the Enter key to see the result.

The first MATCH function, MATCH(B7,INDEX(Table,,1),0), looks for "Pants" in the first column of the Tablerange (A1:A4), and returns 4.

The second MATCH function, MATCH(A7,INDEX(Table,1,),0), looks for "Med" in the first row of the Tablerange (A1:D1), and returns 3.

Then, the INDEX function, INDEX(Table,4,3), returns "30", which is in the fourth row in the third column in the range named Table

INDEX / MATCH -- Example 4

Instead of matching information in column headings, you may need to match information that's stored in the columns, as shown in the table below.

	A	B	C	D
1	Code	Item	Size	Price
2	SW001	Sweater	Small	10
3	JK001	Jacket	Small	30
4	PN001	Pants	Small	25
5	SW002	Sweater	Med	12
6	JK002	Jacket	Med	35
7	PN002	Pants	Med	30
8	SW003	Sweater	Large	14
9	JK003	Jacket	Large	40
10	PN003	Pants	Large	35
11
12	Item	Size	Price	Code
13	Jacket	Med	?	?

In this INDEX / MATCH example, instead of columns headings of Small, Med, and Large, the size is stored in column C. You need to find the price from column D, when Jacket is in column B, and Med is in column C.

1. Set up the worksheet as shown at right
2. Enter the following formula in cell C13:     
   =INDEX(D2:D10,MATCH(1,(A13=B2:B10)*(B13=C2:C10),0))
3. This is an array formula, so hold Ctrl + Shift, and press the Enter key to see the result.

Curly brackets will be automatically added to the formula (don't type them yourself!), so the final result will look like this:

{=INDEX(D2:D10,MATCH(1,(A13=B2:B10)*(B13=C2:C10),0))}

Next, create a similar INDEX/MATCH formula in cell D13, to get the correct code from column A.

Note: You can adjust the ranges to match the data on your worksheet, but in Excel 2003 and earlier versions you can't refer to an entire column.