Excel Index Match Two Criteria

Introduction to Excel Index Match Function with Two Criteria

When working with large datasets in Excel, it’s often necessary to look up specific values based on multiple criteria. The Index Match function is a powerful tool that can be used to achieve this. In this post, we’ll explore how to use the Index Match function with two criteria to retrieve data from a table.

Understanding the Index Match Function

The Index Match function is a combination of two separate functions: Index and Match. The Match function is used to find the relative position of a value within a range, while the Index function returns a value at a specified position from a range. By combining these two functions, you can look up values in a table based on one or more criteria.

Using Index Match with Two Criteria

To use the Index Match function with two criteria, you’ll need to use the following syntax:

=INDEX(range, MATCH(1, (criteria1 * criteria2), 0))

Where:

• range is the range of cells that contains the values you want to retrieve
• criteria1 and criteria2 are the two criteria you want to use to look up the values
• MATCH(1, (criteria1 * criteria2), 0) finds the relative position of the row that meets both criteria

For example, suppose you have the following table:

Name	Department	Salary
John	Sales	50000
Jane	Marketing	60000
Bob	Sales	70000

If you want to find the salary of an employee named “John” in the “Sales” department, you can use the following formula:

=INDEX(C2:C4, MATCH(1, (A2:A4="John") * (B2:B4="Sales"), 0))

This formula looks up the value “John” in the Name column and the value “Sales” in the Department column, and returns the corresponding salary from the Salary column.

💡 Note: Make sure to enter the criteria ranges as arrays, and use the `*` operator to multiply the two criteria together.

Example Use Cases

Here are a few more examples of using the Index Match function with two criteria:

• Finding the price of a product based on its name and category:

=INDEX(D2:D10, MATCH(1, (A2:A10="Product A") * (B2:B10="Category X"), 0))

• Retrieving the address of a customer based on their name and city:

=INDEX(E2:E20, MATCH(1, (A2:A20="Customer Name") * (C2:C20="City"), 0))

• Looking up the phone number of an employee based on their name and department:

=INDEX(F2:F15, MATCH(1, (A2:A15="Employee Name") * (B2:B15="Department"), 0))

Best Practices

When using the Index Match function with two criteria, keep the following best practices in mind:

• Use absolute references for the criteria ranges to avoid errors when copying the formula.
• Make sure the criteria ranges are the same size as the range you’re looking up.
• Use the * operator to multiply the two criteria together, rather than using the AND function.
• Test the formula with different inputs to ensure it’s working correctly.

As we wrap up this discussion on using the Index Match function with two criteria, it’s clear that this formula can be a powerful tool for looking up values in a table based on multiple conditions. By following the examples and best practices outlined above, you can start using this function to streamline your workflow and improve your productivity in Excel.