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.

No comments:

Post a Comment