Using the (Simple) Excel INDEX Function


When I hear the word “Index”, I tend to think of the dictionary definition. Merriam-Webster defines Index as “a list of items (such as topics or names) treated in a printed work that gives for each item the page number where it may be found”.

For example, an index in a printed book would be a list of topics, typically in alphabetical order, and would show what page the topic would be on.

This is a fitting description for the Excel INDEX function, except that the Excel INDEX function does this in reverse. You tell the INDEX function where to look, and it returns the value in that location.

Using the example of the book from the above paragraph, you would pick a page and then list what topics are found on it.

Excel’s INDEX function lets you lookup a value based on the row and column passed to the function.

Start With the Basics

Here’s Excel’s INDEX function:

INDEX(array, row_num, [column_num])

array = a range of cells or an array. It is required.

row_num = the row in the array to return a value from. This is optionally required. See the note below.

Note: If row_num is not included, column_num must be included.

[column_num] = the column in the array to return a value from. This is also optionally required. See the note below.

Note: If col_num is not included, row_num must be included.

Additional Notes

  • If you include both row_num and column_num, the INDEX function returns the value where the two arguments intersect.
  • If you set row_num or column_num to zero, INDEX returns the value in the entire row or column.
  • If row_num or column_num refer to a location outside of array, the #REF! error will be returned.

How to Use Excel INDEX

using the excel index function

Using the above example, entering the formula:

=INDEX(A2:B6, 1, 2)

The A2:B6 tells the function to look in the range of cells from A2 to B6.

The image below highlights the area that it looks in.

excel index range

Highlighted Excel Index Range

The “1” passed to the function tells the function to look in row one of the range, which is equal to cells A2:B2.

The image below highlights the area that it looks in.

excel index row

HIghlighted Excel Index Row

Next, the “2” passed to the function tells the function to look in column two of the range, which is equal to cells B2:B6.

The image below highlights the area that it looks in.

excel index colunn

Highlighted Excel Index Column

Now, if you look at where the two red rectangles intersect, you will notice that there is one cell. This is the cell that is returned by the function.

The result of this function is the value in cell B6, which is “Large”.

excel index intersection

Highlighted Intersection of Excel Index

That’s how you use the function. Now why would you want to use it?

Why You Want to Use The INDEX Function

Now that how it works is explained, why would you want to use it?

Take a scenario where you have a list of items. You can use the INDEX function to look up a specific item in the list.

That’s about it. There’s not much more to say about this function.

But like so many of Excel’s functions, it can be combined with other functions to create powerful tools. Take, for example, combining the INDEX function with the MATCH function. These two, in combination, are an excellent replacement for the VLOOKUP function.

SUMMARY

Excel INDEX is a pretty straightforward function that does one basic task. It looks up a value based on what row and column is passed to it and returns that value.

It’s true power lies in its ability to be combined with other functions to create very powerful Excel tools.

If you have tips or comments on how you use the INDEX function, please let me know.

Leave a Reply