INDEX part 2

Last post I introduced the Index formula. In this post I want to continue explaining how Index works and why you should understand it.

From my previous post, we now know that Index allows you to get a value at an intersection of any give row and column. In this example I return the 4^th row down and the 3^rd column to the right for the data range.

Item	Price	Manufacturer		Result	Formula
Hammer	7.53	ACME West
Bird Seed	13.58	ACME East		ACME West	=INDEX(A2:C11,4,3)
Large Magnet	100.00	ACME South
Rocket Skates	68.54	ACME West
Safety Helmet	18.00	ACME North
Large Spring	240.00	Scotland
First Aid Kit	72.00	ACME East
Cannon Ball	45.00	ACME West
Rope	12.00	ACME North
Paint	22.50	ACME South

Now you can also return an entire row or column from the same table. Perhaps I want to know the total Price of all items in my table. I could write the formula =SUM(INDEX(A2:C11,,2)). And yes I know I could just Sum the column but this is an article on the Index formula. Our example formula ends ,,2 This tells the INDEX formula to return the entire column. If it ended in ,2, then Index would return the row.

Remember the syntax of INDEX. By omitting the Row Or Column reference I can return entire rows or columns.

=INDEX(Range,Row,Column)

Item	Price	Manufacturer		Result	Formula
Hammer	7.53	ACME West
Bird Seed	13.58	ACME East		599.15	=SUM(INDEX(A2:C11,,2))
Large Magnet	100.00	ACME South
Rocket Skates	68.54	ACME West
Safety Helmet	18.00	ACME North
Large Spring	240.00	Scotland
First Aid Kit	72.00	ACME East
Cannon Ball	45.00	ACME West
Rope	12.00	ACME North
Paint	22.50	ACME South

=INDEX’s unique ability to return entire rows or columns from ranges or arrays is very powerful.

Perhaps my dataset is large, spanning cells A2 thru AZ60000 (A2..AZ60000).  I can name that range LargeDataSet

I can then name individual columns in that dataset by referencing the Array. If I want to create another Name for the Index column (column 1) I would write the name formula as follows.

ITEM  =INDEX(LargeDataSet,,1)

This now allows me to lookup any item by using the =MATCH function.

Item	Price	Manufacturer		Result	Formula
Hammer	7.53	ACME West
Bird Seed	13.58	ACME East		5	=MATCH("Safety Helmet",Item)
Large Magnet	100.00	ACME South
Rocket Skates	68.54	ACME West		18.00	=OFFSET(A1,MATCH("Safety Helmet",Item,0),1)
Safety Helmet	18.00	ACME North
Large Spring	240.00	Scotland
First Aid Kit	72.00	ACME East
Cannon Ball	45.00	ACME West
Rope	12.00	ACME North
Paint	22.50	ACME South

In this example I use my name Range Item (A2..A30) to find the row Safety Helmet is on (row 5)

I then use Offset much like I would use Vlookup to return the price.

Using this method is comparable in speed to using Vlookup  when you are doing only 1 lookup. But I can look to the left with this method (Vlookup only looks to the right).

INDEX is useful in many other ways but that another future post.  Give INDEX a try and add it to your Excel knowledge.