Unlike the MATCH function, which gets the position of an item in a list or a 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 and 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 a city at a given position in this list; let’s say the 3rd position. In its simplest form, INDEX takes just two arguments: an array and a row number. So in this case, we give INDEX an array, which is just a reference to the city name, and then point back to the position for the row number. INDEX correctly retrieves Chicago from the list of cities. If I put in “13” we’ll get Austin, the 13th city in the list. So far, we’re just using INDEX to retrieve an item from a single column based on its position. Now let’s expand on this to use INDEX with multiple columns. First, to make the formulas easier to read, I’ll name the 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 one more argument: the column number from which to get the city name. In this case, that’s the column number “1”. 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’re probably 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
