INDEX

The INDEX formula is one of the top 10 formulas in Excel that you need to understand.

The INDEX formula calculates a value or the reference to a value your data.

Now that sounds complex, but it’s not and when you see what it can do, you will want to use it.

SYNTAX

1. INDEX (range or table, row number, column number)
This will give you the value or reference from given range.

2. INDEX (range, row number, column number, area number)
This will give you the value or reference from specified area at row / column coordinate.

Let’s say you have a list of items that you sell in your store, price of the item and manufacturer of the item and you want to get the name of the item in the 10th row.

You can use =INDEX(itemlist,8) where itemlist is the range of rows where you have your list.

Now you can use that result to get the manufacturer in the third column using.  =INDEX(itemlist,10,3)


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

In my next post I will continue showing how to use Index.