Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to look things up with INDEX

Summary 
In this video, we look at how to configure INDEX to fetch a value by row and column. This is half of the INDEX/MATCH equation.
Video Transcript 

What does the INDEX function do?

Unlike the MATCH function, which gets the position of an item in a list or table, INDEX assumes you already know the position, and it gets the value of the item at that position.

Let's take a look.

INDEX is a powerful and flexible function that can be used for advanced features, like dynamic ranges, two-way lookups.

However, in this example, we're going to use INDEX for the simplest use case possible - retrieving items from a known position.

We're going to use INDEX to look up the city in a given position in this list, let's say the 3rd position.

In it's simplest form, INDEX takes just 2 arguments, an array and a row number.

So, in this case we give INDEX an array, which is just a reference to the city names, and then point back to the position for row number.

INDEX correctly retrieves Chicago from the list of cities. If I put in 13 we get Austin, the 13th city in the list.

So far, we're just using INDEX to retrieve an item in a single column based on it's position. Now let's expand on this to use INDEX with multiple columns.

First, to make the formulas easier to read, I'll name ranges for both the city data and the position. Now, I'll add the first INDEX formula to retrieve the city name.

Because we're now using index on a multi-column table, I need to add on more argument -- the column number from which to get the city name. In this case, that's the column number one.

Now I can copy the formula down and come back and adjust the column numbers for each INDEX formula.

As you can see, we can easily retrieve all the information for a given city with INDEX by supplying the right row and column numbers.

Now, at this point, you might be feeling a little underwhelmed. So what if you can retrieve something when you know the position, you might be thinking, you hardly ever know the position of anything in a worksheet.

Yes, that's true, and that's where the MATCH function comes in. MATCH can find the position of an item. And, when you use INDEX together with MATCH, you can do everything VLOOKUP can do, and a lot more too.

Author 
Dave Bruns