Excel公式匹配问题：判断列内容匹配返回结果时出现#NAME?错误求助

Let's break this down step by step, starting with the simplified problem you're stuck on, then circling back to your original broader need.

The Simplified Issue: Check if E2 exists in Column G #

Your current formula is throwing a #NAME? error because of a misstep in how you're using the ADDRESS function—you passed the column letter "I" as the second argument, but ADDRESS requires a column number (like 9 for column I). Excel interprets "I" as a named range that doesn't exist, hence the error.

But wait, your goal here is just to return "MATCH" when E2 is found in G:G, right? You don't need to reference the I column in the formula itself. Here are two clean, error-free solutions:

1. Using COUNTIF (simplest approach)

   =IF(COUNTIF(G:G, E2) > 0, "MATCH", "NO MATCH")
   

   This counts how many times E2 appears in G:G. If the count is greater than 0, it returns "MATCH"; otherwise, "NO MATCH".

2. Using IFERROR with MATCH (more explicit)

   =IFERROR(IF(MATCH(E2, G:G, 0), "MATCH"), "NO MATCH")
   

   MATCH returns the position of E2 in G:G if found; if not, it throws an error. IFERROR catches that error and returns "NO MATCH" instead.

Your Original Requirement: Match A/B Columns against C/D Columns #

Since you mentioned you started with a more complex need—checking if either the value in column A or B appears anywhere in columns C or D—here's a formula that handles that:

=IF(COUNTIF(C:D, A2) + COUNTIF(C:D, B2) > 0, "MATCH", "NO MATCH")

This adds the counts of A2 in C:D and B2 in C:D. If either count is positive, the total will be greater than 0, so it returns "MATCH".

Quick Recap of Why Your Original Formula Failed #

The #NAME? error stemmed from two issues:

• Passing a column letter (I) to ADDRESS instead of the numeric column index (9)
• Trying to reference the I column in the formula when you only needed to return static text "MATCH"

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