Microsoft Excel is an incredibly powerful tool that is widely used in various fields such as finance, data analysis, accounting, and more. One of the most important features in Excel is the ability to use formulas that can quickly perform complex calculations, and there are many different types of formulas available.

Two of the most commonly used formulas in Excel are INDEX and MATCH. INDEX and MATCH formulas are often used together to extract data from large datasets without having to scroll through them manually. In this article, we will discuss how to use INDEX and MATCH in Microsoft Excel.

What is INDEX and MATCH in Excel?

INDEX and MATCH are two separate formulas in Excel that can be combined to create powerful data retrieval functions. INDEX returns a value or reference to a cell at a specified position within an array, while MATCH returns the position of a specified value within a range.

Using INDEX and MATCH together, you can look up a value in one table and return corresponding data from another table. This makes it easier to work with large datasets and speeds up the process of finding relevant data.

How to Use INDEX and MATCH in Excel

The syntax for the INDEX and MATCH formulas is as follows:

=INDEX(array, row_num, [column_num])

=MATCH(lookup_value, lookup_array, [match_type])

Before using INDEX and MATCH together, you need to have two sets of data: one that contains the data you want to retrieve, and one that contains the criteria you want to use to look up the data. The two data sets should have at least one column in common that you can use to match them up.

Step 1: Specify which column to match

The first step to using INDEX and MATCH is to specify which column you want to match on. For example, let’s say you have two tables. Table 1 includes a list of employee names and their corresponding IDs, and Table 2 includes a list of items that employees have claimed.

To use INDEX and MATCH to retrieve the ID of an employee who has claimed a particular item, you need to first specify which column in Table 1 contains the employee names that correspond to the item claims in Table 2. In this case, it would be the first column in Table 1 (column A).

Step 2: Use the MATCH formula

Next, you need to use the MATCH formula to find the index number of the employee’s name in Table 1. The MATCH formula has three arguments:

lookup_value: This is the value you want to find, in this case the employee’s name.

lookup_array: This is the range of cells that contains the lookup value, in this case, the first column of Table 1.

match_type: This is an optional argument that specifies whether you want an exact match or an approximate match. We’ll use “0” for an exact match.

To use the MATCH formula in this scenario:

=MATCH(“Employee Name”, Table1[Employee Name], 0)

Here, “Employee Name” is the value you want to match, Table1[Employee Name] is the range you want to search, and 0 indicates that you want an exact match.

Step 3: Use the INDEX formula

Now that you’ve found the index number of the employee’s name, you can use the INDEX formula to retrieve the employee’s ID from Table 1. The INDEX formula has three arguments:

array: This is the range of cells that contains the data you want to retrieve, in this case, the second column of Table 1.

row_num: This is the index number of the row that contains the data you want to retrieve, which we found in Step 2 using the MATCH formula.

column_num: This is an optional argument that specifies the column number of the data you want to retrieve. In this case, we want to retrieve data from the second column, so we’ll leave this argument blank.

To use the INDEX formula in this scenario:

=INDEX(Table1[Employee ID], MATCH(“Employee Name”, Table1[Employee Name], 0))

Here, Table1[Employee ID] is the range of cells that contains the data we want to retrieve, the MATCH formula finds the row number of the employee’s name, and the third argument in INDEX is left blank since we want to retrieve data from the second column.

Conclusion

Using INDEX and MATCH in Excel can significantly improve your data retrieval process, especially when you’re dealing with large datasets. By following these steps, you can easily and quickly extract relevant data and eliminate the need for manual searching.