# INDEX part 2

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 4th row down and the 3rd 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.