vlookup is useful function but it has limitations. In this post you can learn how to level-up your Excel game and wow your colleagues with index-match.

This post is about learning how to use the **index-match**function. A companion post provides advice on troubleshooting common problems.

The Challenge: Selecting values from an Excel table

**vlookup** and **index-match**functions are useful when you’re facing the following challenge:

Challenge: Select a value from an Excel table by matching rows and/or columns.

In this simple example you could just copy-paste the value, but that will fail in more complicated cases. That’s where “lookup” functions come in handy.

Lookup example: Take a list of 100 customer emails and select their phone numbers from a larger table of 1000 customers.

The Common Option: vlookup

=VLOOKUP(B10,B3:E6,3,0)

The [**vlookup** function](https://www.wallstreetoasis.com/resources/excel/study/vlookup-guide) is most people’s go-to because it’s powerful and easy to understand. There are lots of tutorials around so we won’t go into detail here.

The problem with vlookup is that it has some quite serious limitations:

• If someone adds or deletes a column after you enter the formula, it will return an incorrect result (wrong column) 😱

• The lookup value always has to be in the first column of the table (annoying if the data you want to select are to the left of the index values)

• It’s easy to make mistakes counting columns in large tables

Level-up: Index-Match

**index-match** is similar to **vlookup** but far more powerful and versatile. 💪 Spending 15 minutes to learn it will let you work small miracles and wow your co-workers forever (promise, it’s been working for me for years).

**index-match** is actually a combination of three functions: one [**index** function](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd) around two [**match** functions](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a). Simply, it works like this:

***💡 Basic game plan for i`ndex-match

From a table, return the value in a specified row and column (index). Find the row by matching a value with the row names (row match). Find the column by matching a second value with the column names (row match).

Syntax: index(table, row match, column match)

We’ll cover each of the functions individually and then put them together.

Index a Table

=INDEX(B3:E6,3,3)

The [**index** function](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd) returns a value from a table using row and column indexes:

• Take a table (blue area B3:E6)

• Take the row from the first index**** (here = 3)

• Take the column from the second index (here also = 3)

• Return the value in that cell (3rd row, 3rd column: D5 = 6)

So far so good, but still we’re fetching the value using hard-coded row and column indexes (yuk). We’ll fix that with matches.

Match x2

The [**match** function](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a) looks at an array (row or column) and returns the position (not the cell value!) of the first match with a lookup value.

=MATCH(B10,B3:B6,0)

The first example shows a **row match** within a column:

• Take the row lookup value (“Bananas” from B10)

• Check the row names**** (red column in B3:B6)

• Return the row position where the lookup value was found. Here the result is 3 because “Bananas” is in third place from the top (including the empty cell). Remember that match gives you the row’s position and not its value.

• Note the third parameter = 0. It’s the match type and it should always be included (!) and always be zero unless you know what you’re doing.

=MATCH(C9,B3:E3,0)

The second example shows a **column match** within a row. It’s works exactly the same as the row match, just horizontally instead of vertically:

• Take the column lookup value (“Cereal 2” from C9)

• Check the column names**** (red row in B3:E3)

• Return the column position where the lookup value was found. Here it’s 3 because “Cereal 2” is 3rd place from the left (including the empty cell)

Putting Index-Match together

Now it’s time to put it all together:

=INDEX($B$3:$E$6,MATCH($B11,$B$3:$B$6,0),MATCH(C$10,$B$3:$E$3,0))

Okay, I admit it looks a bit scary. But you know all the pieces already! Remember the basic game plan:

Index (table, row match, column match)

The formula works like this:

• **index** the table (blue cells B3:E6)

• **row match**: Check for the row lookup value (“Bananas” from red B11) in the row names** (purple B3:B6) and return the row number = 3

• **column match**: Check for the column lookup value (“Cereal 2” from green C10) in the column names (pink B3:E3) column number = 3

• Return the**** value from the table in row and column numbers (row = 3, column = 3). The result is D5 = 6.

Yeah, you did it! 🎉

Advantages of index-match

So what do you get for all that work? Lots!

**index-match** has some significant advantages over **vlookup**:

• It’s robust towards adding or removing rows or columns in the data table

• You can dynamically index by row and column name (e.g. give people two input fields)

• The row index can be in any column (doesn’t need to be to the left of the data)

• Using names is less error-prone than typing a column number (easier to understand what’s happening)

You can see how powerful it is in the example below: Using just one function, you can completely re-order and down-select the original table. Just type in the row and column names you want — magic! 🪄

That’s it! Now you know all about **index-match**. If you have questions or if I missed anything, let me know in the comments!

Happy Excelling.

To try Curiosity for free, download it at curiosity.ai. Follow us on Twitter or LinkedIn to hear first about new features.