Excel中无法实现跨表匹配更新TRANSLATION列的问题求助（已尝试VLOOKUP函数）

Hey there! Let's get that cross-sheet merge sorted out for you—you were on the right track with VLOOKUP, so let's figure out where it might have gone wrong and walk through the correct steps.

Why your VLOOKUP might have failed #

Chances are one of these common issues tripped you up:

• You didn't set exact match (the final FALSE parameter) — VLOOKUP defaults to approximate match, which won't work for your unique KEY/Let values.
• You referenced the wrong range in Sheet2, or didn't lock the range with $ signs (so it shifted when you dragged the formula down).
• You didn't handle cases where no match exists, leading to #N/A errors that overwrote your existing TRANSLATION values.

Solution 1: Fixing VLOOKUP (works for all Excel versions) #

Since you want to keep existing TRANSLATION values when there's no match, we'll use a temporary column to avoid circular references:

1. Insert a temporary column (e.g., Column D) in Sheet1, then copy all values from Column C (your original TRANSLATION) into Column D. This acts as a backup.
2. In Sheet1's cell C2 (the first data row under TRANSLATION), enter this formula:

   =IFERROR(VLOOKUP(A2, Sheet2!$A:$C, 3, FALSE), D2)
   

3. Drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to all rows.
4. Once you're happy with the results, select Column C, right-click > Copy, then right-click again > Paste Values (this replaces the formula with static text). You can now safely delete the temporary Column D.

Breakdown of the formula:

• VLOOKUP(A2, Sheet2!$A:$C, 3, FALSE): Looks up the KEY value in A2 against Sheet2's Let column (Column A). When a match is found, it returns the value from the 3rd column of Sheet2's range (TRANSLATION). The FALSE ensures we only get exact matches.
• IFERROR(..., D2): If no match is found (VLOOKUP returns #N/A), it uses the original TRANSLATION value from our backup column D instead.

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

If you're on a newer Excel version, XLOOKUP is simpler and more intuitive (no need to worry about column order in your lookup range):

1. Follow step 1 from the VLOOKUP solution to back up your original TRANSLATION to Column D.
2. In Sheet1's cell C2, enter:

   =IFERROR(XLOOKUP(A2, Sheet2!$A:$A, Sheet2!$C:$C), D2)
   

3. Drag the formula down, then paste values and delete the temporary column just like before.

Breakdown:

• XLOOKUP(A2, Sheet2!$A:$A, Sheet2!$C:$C): Directly looks up A2 in Sheet2's Let column, and returns the corresponding value from Sheet2's TRANSLATION column. It defaults to exact matches, so no extra parameters needed.
• IFERROR again handles missing matches by keeping your original values.

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