Excel公式修改需求：多匹配结果下判定是否符合指定条件

Absolutely, this is totally feasible with a standard Excel formula—no macro required for most cases, which is great because formulas are dynamic and don’t require enabling macros (a plus for security and ease of use).

Simplified Formula Using COUNTIFS #

Instead of relying on INDEX/MATCH (which only grabs the first match), we can use COUNTIFS to count how many rows meet both conditions at once:

• Column B equals the value in L2
• Column M equals the value in M2

If that count is greater than 0, it means at least one matching row exists, so we return 1; otherwise, 0. Here’s the formula:

=--(COUNTIFS(B:B, L2, M:M, M2) > 0)

Or if you prefer the explicit IF version (easier to read for some):

=IF(COUNTIFS(B:B, L2, M:M, M2) > 0, 1, 0)

How This Works #

• COUNTIFS(B:B, L2, M:M, M2) counts all rows where B matches L2 AND M matches M2.
• The > 0 checks if there’s at least one such row (returns TRUE/FALSE).
• The double negative (--) converts the boolean result to 1 (TRUE) or 0 (FALSE)—a handy Excel trick to avoid writing a full IF statement.

Why This Is Better Than Your Original Formula #

Your original INDEX/MATCH setup only checks the first occurrence of L2 in column B. The COUNTIFS approach inherently handles all matches, so you don’t have to worry about missing rows where L2 appears multiple times but only one of them has M equal to M2.

Should You Use a Macro? #

A macro would work, but it’s overkill for this specific task unless you’re dealing with:

• Extremely large datasets (100k+ rows) where you need to optimize performance (though COUNTIFS is surprisingly efficient for most cases).
• A more complex workflow where this check is part of a larger automated process (like generating reports, bulk updates, etc.).

For a simple, dynamic check that updates automatically when your data changes, the formula is the way to go.

内容的提问来源于stack exchange，提问作者xLokos