Excel映射需求：通过A列与C列匹配生成新D列

Solution for Adding Column D in Excel #

Alright, let's tackle this Excel column mapping task. Here's how you can add the D column as requested using built-in Excel functions:

Step 1: Understand the Mapping Logic #

We need to check if each value in Column C exists in Column A. If it does, pull the corresponding value from Column B; if not, leave the cell blank.

Step 2: Use XLOOKUP (Recommended for Excel 365/2021+) #

XLOOKUP is more straightforward and flexible for this scenario. In cell D2, enter this formula:

=XLOOKUP(C2, A:A, B:B, "")

• C2: The value we want to search for (from Column C)
• A:A: The range to search in (Column A)
• B:B: The range to return a value from (Column B, matching the row where the match was found in Column A)
• "": The value to return if no match is found (blank cell)

After entering the formula, press Enter, then click and drag the fill handle (the small square at the bottom-right corner of D2) down to apply the formula to all rows in Column D.

Step 3: Alternative for Older Excel Versions (VLOOKUP) #

If you're using an older Excel version that doesn't support XLOOKUP, use this formula in D2:

=IFERROR(VLOOKUP(C2, A:B, 2, FALSE), "")

• VLOOKUP(C2, A:B, 2, FALSE): Searches for C2 in the first column of range A:B, returns the value from the 2nd column (Column B) with exact match
• IFERROR(..., ""): Returns a blank cell instead of an error when no match is found

Again, drag the fill handle down to apply the formula to all rows.

Final Result #

After applying either formula, your spreadsheet will look exactly like the expected output:

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