Excel IF+VLOOKUP+MATCH组合公式#REF!报错排查及行范围限定实现咨询

Let's break down your issues and fix them step by step:

1. Why you're getting the #REF! error #

Your original formula uses VLOOKUP($U7335,'2022_Contracts.xlsx'!$T:$T, MATCH(...), FALSE) — here's the root cause:

• The lookup range for VLOOKUP is '2022_Contracts.xlsx'!$T:$T, which is only 1 column wide.
• The third argument of VLOOKUP expects a column number relative to the lookup range, not the entire worksheet. Your MATCH($E7335,'2022_Contracts.xlsx'!$F:$F,0) returns the worksheet column number for column F (which is 6), and since your lookup range only has 1 column, asking for column 6 triggers the #REF! error.

2. How to restrict the check to only the row matched by E7335 #

Your goal is clear:

1. Locate the row in '2022_Contracts.xlsx' where column F equals $E7335
2. Verify if column T in that exact row matches $U7335
3. Return "Match" or "No Match" based on that check

VLOOKUP isn't built for this row-specific validation because it searches an entire column for the lookup value. Instead, use INDEX + MATCH (works in all Excel versions) or XLOOKUP (for newer Excel releases) to target the specific row first.

Solution 1: INDEX + MATCH (compatible with all Excel versions)

Use this formula:

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

Here's what each part does:

• MATCH($E7335,'2022_Contracts.xlsx'!$F:$F,0): Finds the exact row number where column F matches $E7335. Returns #N/A if no match exists.
• INDEX('2022_Contracts.xlsx'!$T:$T, ...): Pulls the value from column T in that matched row.
• The IF statement compares that pulled value to $U7335 and returns "Match" or "No Match".
• IFERROR catches the #N/A case (when $E7335 isn't found in column F) and returns "No Match".

Solution 2: XLOOKUP (for Office 365/Excel 2021+)

If you have access to XLOOKUP, this formula is more concise and readable:

=IFERROR(IF(XLOOKUP($E7335,'2022_Contracts.xlsx'!$F:$F,'2022_Contracts.xlsx'!$T:$T)=$U7335, "Match", "No Match"), "No Match")

XLOOKUP directly fetches the value from column T that corresponds to $E7335 in column F, then we just compare it to $U7335.

Key Takeaway #

Your original formula tried to force VLOOKUP to handle a task it wasn't designed for (targeting a specific row after matching a different column). INDEX + MATCH or XLOOKUP are better suited for this row-specific check, and they eliminate the #REF! error by working with row numbers instead of relative column positions.

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