为IF、AND、VLOOKUP组合公式添加同行匹配条件

Absolutely! Your current formula only checks if each value exists independently in their target columns, but it doesn’t confirm they’re paired on the same row in the 2022_Contracts.xlsx file. Here are two straightforward solutions to fix this:

Method 1: Use COUNTIFS (Simplest Approach) #

The COUNTIFS function is perfect here because it counts rows that meet multiple criteria across different columns. If the count is greater than 0, that means your two values appear together on the same row.

=IF(COUNTIFS('2022_Contracts.xlsx'!$T:$T, $U7335, '2022_Contracts.xlsx'!$F:$F, $E7334) > 0, "Match", "No Match")

• Breakdown: This formula counts how many rows in 2022_Contracts.xlsx have $U7335 in column T and $E7334 in column F. If there’s at least one such row, it returns "Match"; otherwise, "No Match".

Method 2: Use MATCH + INDEX (More Transparent for Troubleshooting) #

If you want to explicitly check the paired value in the matching row, this combination works well. We’ll add IFERROR to handle cases where the first value isn’t found at all.

=IF(IFERROR(INDEX('2022_Contracts.xlsx'!$F:$F, MATCH($U7335, '2022_Contracts.xlsx'!$T:$T, 0)) = $E7334, FALSE), "Match", "No Match")

• Breakdown:
  1. MATCH($U7335, '2022_Contracts.xlsx'!$T:$T, 0) finds the row number where $U7335 appears in column T (the 0 ensures an exact match).
  2. INDEX('2022_Contracts.xlsx'!$F:$F, [row number]) pulls the value from column F at that row.
  3. We compare that pulled value to $E7334—if they match, we get TRUE; IFERROR catches cases where $U7335 isn’t found and returns FALSE.
  4. The outer IF converts that boolean result to "Match" or "No Match".

Why Your Original Formula Didn’t Work #

Your original AND(VLOOKUP(...), VLOOKUP(...)) only checks if each value exists somewhere in their columns—even if they’re in totally different rows, it would still return "Match". The solutions above fix this by tying the two criteria to the same row.

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