INDEX and MATCH are used in Excel to implement multi-condition lookup, which can be done by array formulas or auxiliary columns. 1. Use array formula: =INDEX(C:C, MATCH(1, (A:A="Zhang San")*(B:B="Sales Department"), 0)), which is suitable for direct input of the new version of Excel or press Ctrl Shift Enter to confirm with the old version; 2. Use auxiliary columns: After splicing the contents of column A and B in column D, use =INDEX(C:C, MATCH("Zhang San Sales Department", D:D, 0)) to search; 3. Return multiple results to use the FILTER function; 4. Pay attention to avoid references in the whole column, considering performance optimization, processing errors, etc.

In Excel, using the combination of INDEX and MATCH can achieve very flexible search functions, especially when it is necessary to find data based on multiple conditions (multi-condition matching). The following are several common practices and precautions.

Basic structure review

The INDEX function is responsible for returning data at a specified location in a certain area, and MATCH is responsible for finding the location of a certain value. The basic formula is:

 =INDEX(result area, MATCH(find value, find area, 0))

But when you have multiple conditions, you need to transform the MATCH part so that it can handle multiple judgment conditions.

Standard writing method for multi-condition search

The most common way is to use array formulas (press Ctrl Shift Enter in the old version of Excel), and the new version of Excel (such as 365 or 2021) can directly enter the formula.

Suppose you have a table where column A is the name, column B is the department, and column C is the score. If you want to find out the score of "Zhang San" in the "Sales Department", you can write it like this:

 =INDEX(C:C, MATCH(1, (A:A="Zhang San")*(B:B="Sales Department"), 0))

• (A:A="張三") is the first condition;
• (B:B="銷售部") is the second condition;
• The result of multiplication of two conditions is a logical array, and only rows that meet at the same time will be 1;
• MATCH(1, ... , 0) finds the first location that meets the criteria;
• Then use INDEX to return the corresponding score.

Note: If you are using an old version of Excel, press Ctrl Shift Enter to confirm after entering the formula, otherwise an error will occur.

Simplify operations with auxiliary columns

If you don't want to use array formulas, you can also add a "auxiliary column" to splice multiple conditions, and then use ordinary INDEX MATCH.

For example, enter the formula in column D:

 =A2 & B2

Then when searching, you can write it:

 =INDEX(C:C, MATCH("Zhang San Sales Department", D:D, 0))

This method is suitable for scenarios with small data volumes. The advantage is that it is easy to understand and maintain, and the disadvantage is that it adds an additional column.

What to do with multiple results?

All the above examples return only the first match. If you want to return multiple results, such as all records of "Zhang San" in the "Sales Department", you need to use the FILTER function (suitable for Excel 365/2021 and above):

 =FILTER(C:C, (A:A="Zhang San")*(B:B="Sales Department"))

This returns a list containing all the results that meet the criteria.

If you can only use INDEX MATCH, it requires more complex nested or auxiliary counting sequences to achieve, which is relatively complex.

Tips and FAQs

• Avoid whole column references : Although writing methods like A:A are convenient, they will cause performance degradation in big data tables. It is recommended to limit the scope, such as A2:A100.

• Note case : Excel is case-insensitive by default, so "ABC" and "abc" will be considered the same.

• Error handling : You can wrap the entire formula with IFERROR to prevent #N/A errors:

   =IFERROR(INDEX(...), "Not Found")

  ---

  Basically these are the methods. Multi-condition search of INDEX MATCH is actually not difficult. The key is to understand the logic of array formulas and choose the appropriate writing method according to different situations.

  The above is the detailed content of excel index match for multiple criteria. For more information, please follow other related articles on the PHP Chinese website!