How to Use Index Match in Excel

Today I want to take you through one of my favorite formula combinations, Index Match in Excel.

Index Match is an incredibly powerful function in Excel that has saved me more times than I can count. It allows you to quickly and easily look up data. With this combination, you can save time by not manually searching for the information you need. Plus, it’s easy to use and understand!

Learn how to use Index and Match in Excel today with my step-by-step tutorial!

Key Takeaways

The Index Match function in Excel is a powerful combination of two Excel functions: INDEX and MATCH. Combining these two functions lets you quickly look up data without manually searching through your spreadsheet. Instead, it uses an index to find the information you’re looking for in a fraction of the time.

Here’s how it works: INDEX(MATCH()) uses two arguments, one for the row and one for the column. It looks through your data set and finds a match based on these two arguments. Once it finds a match, it returns the value associated with the row and column of that match.

For example, if you have a list of employee names and salaries, you can use Index and Match to quickly find an employee’s salary based on their name. All you need to do is specify the row argument (the employee name) and the column argument (the salary).

The power of Index Match lies in its flexibility. You can use it to do various tasks, such as finding the highest value in a range of numbers, looking up data from multiple worksheets, and more.

What Is An Index?

An Index is an array of information you can use to quickly look up data. It’s essentially a way of organizing and categorizing your data in Excel so you can find what you’re looking for without manually searching through the entire spreadsheet.

There are several different types of indices, such as row-based, column-based, and matrix indices. For example, if you have a data chart with row or column headings, then you have a row-based or column-based index. Along those lines, a rudimentary Profit and Loss statement is a matrix index. It is a table of financial information (structured data) organized by month (column header) and type of revenue/expense.

To evaluate the index, you can use VLOOKUP() on the revenue/expenses, HLOOKUP() on the months, SUMIF() on either category and INDEX(MATCH()) on the entire data set. As you might guess from the name, INDEX(MATCH()) is the most powerful tool to evaluate an index.

In addition, you can even go one step further and create a dynamic index that allows you to evaluate the same data set in different ways.

How To Use The INDEX And MATCH Functions

The INDEX Function

Here is the INDEX formula syntax:

=INDEX(Array, Row_Num, [Column_Num])

• Array (required) – a continuous data set reference, index, or table range. Excel can reference this data set using a range.
• Row_num (required) – counts to the row number specified and can be either a numerical value, a cell reference containing a numerical value, or a formula (like the MATCH function).
• Column_num (optional) – counts to the column number specified and can be either a numerical value, a cell reference containing a numerical value, or a formula (like the MATCH function.

The MATCH Function

Here is the MATCH formula syntax:

=MATCH(lookup_value, lookup_array, [match_type])

• Lookup_value (required) – an item from the row or column headers of the index that we want to find.
• Lookup_array (required) – is the range of the column or row header itself. To avoid confusion, it is a best practice to align the “lookup_array” to the dimensions of the index.
• Match-type (optional) – defines how Excel returns a result if multiple potential matches exist. You can select -1, 0, and 1 for “match-type.” If you don’t enter a value, Excel will default to 1. Typically you will want to use 0 to return an exact match. -1 and 1 return the nearest or approximate match.

Using INDEX and MATCH together

We will use MATCH() as part of the INDEX() function to generate the “row_num” and the “column_num” since Match can return the position in a range.

Where To Find The INDEX Function and MATCH Function?

Option 1:

You can find the INDEX and MATCH functions in the Formulas tab of Excel under Lookup & Reference.

Option 2:

You can use Formula Builder to walk you through the formula step by step.

Option 3:

You can manually type the formula into any cell.

Example: Using Index And Match In Excel

Excel Workbook

Example: Evaluate a Profit and Loss Statement

Now, let’s review an example of how to use Index Match in Excel. For example, we are going to pull specific expense information out of a Profit and Loss statement.

Step 1: Create an Index

First, we will lay out a two-dimensional data set, in this case, a Profit and Loss statement. Then, we will use the column headers (months) and row headers (revenue and headers) in the next step.

Step 2: Create Criteria to Evaluate

Next, we need to select criteria to evaluate. Let’s assume that we want to compare Cost of Sales and Compensation expense between the most profitable month (April) and the least profitable month (January). We will add a table to pull this information.

Step 3: Add the Index Match functions

Finally, we will write the Index Match functions to return values from the data set. In turn, Excel will run the analysis.

For the =INDEX(Array, Row_Num, [Column_Num])formula, the array is the data table shown in blue below. Row_num and column_num will be Match formulas.

The first =MATCH(lookup_value, lookup_array, [match_type]) formula will pull row. The lookup value is in red in the analysis table. Excel highlighted the lookup array in the row axis and showed it in dark purple. The second =MATCH(lookup_value, lookup_array, [match_type]) formula will pull column. The lookup value is in green in the analysis table. Excel highlighted the lookup array in the column axis and showed it in light purple.

Once complete, this formula will return a clean analysis as shown in the following table. In addition, you can use any combination of months or revenue/expenses for analysis.

Additional Ways To Use Index And Match

Two-Way Lookup

Did you know you can use INDEX MATCH to do a two-way lookup? You just have to input a MATCH function for both the row_num and column_num variables. With this strategy, the MATCH function returns both row and column numbers. Then Excel will look across the columns and down the rows to find both matches, and the Index function returns the result.

Looking Up Multiple Criteria

You can use an array formula to lookup multiple items at once. This is useful when you have more than two criteria that you want to use for a search. Combining INDEX MATCH and the ARRAY formula allows you to look up data from multiple columns and rows simultaneously.

Looking Up The Highest, Lowest, Or Average Value In An Index

Microsoft Excel has special functions to find a minimum, maximum and average value in a range. But what if you need to get a value from another cell that is associated with those values? In this case, use the MAX, MIN or AVERAGE function together with INDEX MATCH. Add MAX, MIN, or AVERAGE as the relative reference inside the MATCH function.

Avoid Errors When Using Excel Index And Match

If you want to give a clearer message than the #N/A error when using an INDEX MATCH formula in Excel, you can enclose the formula in the IFNA function. This will not change the behavior of the formula but will display a more meaningful message. You can use IFERROR if you want to catch any errors.

Use Index And Match Versus VLOOKUP Function In Excel

While VLOOKUP has its place, and the lookup formula is a bit easier to use, the Excel combo of Index and Match is far more powerful. Here are some benefits of using Index Match instead of VLOOKUP:

• Right to left lookup – with VLOOKUP, you can only look from left to right, but with Index and Match, you can also look from right to left.
• More flexible – the lookup value does not need to be in the first column of the lookup range
• Speedier results – even though both formulas are fast, Excel Index Match typically runs faster than VLOOKUP when dealing with large databases or a complex Excel file.
• Mixing data types – using Index and Match allows you to mix data types (i.e., numbers, text) within the lookup array without producing errors, whereas VLOOKUP will generate errors if different
• Insert or delete columns safely – Index and Match is a lot more flexible than VLOOKUP, which requires a specific column number to count to

Tips and Tricks

1) Are You Seeing Double?

INDEX MATCH will only return the first reference that it comes across. With that in mind, make sure to remove any duplicates from your data set, or consider another formula like SUMIF should the duplicates be needed.

2) Check Your Formatting

The function is extremely sensitive. Make sure to watch out for extra spaces, numbers formatted as text, references over 255 cells, or other items that don’t match perfectly.

3) Use the Formula Builder

If you’re having trouble, use Excel’s built-in Formula Builder. This feature can help walk you through the process step by step and ensure that your formulas are correct.

Frequently Asked Questions

Why is INDEX match better than VLOOKUP?

Quicker processing times, greater accuracy, and the ability to return multiple values are reasons why INDEX MATCH is often preferable to VLOOKUP. Its flexibility can save time and energy compared to VLOOKUP’s more basic approach. In addition, it’s easier to maintain since references update automatically if you change column order or add/delete rows of data. Overall, using INDEX and MATCH offers more control over your results than VLOOKUP does.

Can you use XLOOKUP instead of INDEX match?

Yes, you can use XLOOKUP as an alternative to INDEX MATCH. It offers many of the same features and benefits as INDEX MATCH but has a more straightforward syntax that allows for simpler formula building and more control over your results. Additionally, XLOOKUP supports the ability to search multiple columns and return corresponding values from another column or range. XLOOKUP also has a built-in option to search for an exact match or closest match and can search from right to left and left to right. Ultimately, XLOOKUP offers more features than INDEX MATCH and is the preferred method for many Excel users.

However, it is essential to note that INDEX MATCH still offers advantages over XLOOKUP in specific scenarios. For example, INDEX MATCH is often the better option if you need to search for multiple criteria or use an array formula. Additionally, XLOOKUP may not be available in some versions of Excel, and therefore it is crucial to understand how both functions work.

Is VLOOKUP Or Index Match Faster?

Even though both formulas are fast, Index Match typically runs faster than VLOOKUP when dealing with large databases.

How Do I Develop Excel Skills Like Using Index and Match?

Learning Excel and its many formulas can be daunting. Fortunately, there are various ways to learn the necessary skills, such as online courses or tutorials. You can also practice by creating your own sample data sets – this is an excellent way to build your understanding of INDEX MATCH and XMATCH functions.

Finally, it is best to practice using these functions with real data. This allows you to become familiar with the logic behind each one and be able to transfer your understanding to new situations. Also, don’t be afraid to experiment and make mistakes – this is the best way to gain knowledge in Excel.

Have any questions on how to use Index Match in Excel? Are there other topics you would like us to cover? Leave a comment below and let us know! And make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.