如何在Excel中合并不同工作表的分支代码关联映射数据？

Absolutely! This is a super common task in Excel, and there are a few straightforward, reliable ways to merge these mappings without breaking your existing relationships. Let me break down the best methods for you:

This is the easiest go-to for quick merges, especially if you're new to Excel formulas:

• First, create a new worksheet (or use a blank section in one of your existing sheets) as your merged result table. In the first column, list all unique Branch codes (you can copy codes from both sheets, then use Data > Remove Duplicates to clean them up).
• To pull in the corresponding zip codes: In cell B2 (next to your first Branch code), enter this formula:
  =VLOOKUP(A2, Sheet1!$A$2:$B$100, 2, FALSE)
  Let's break this down:
  • A2 = the Branch code you're matching
  • Sheet1!$A$2:$B$100 = the range of your first sheet that contains Branch codes (column A) and zip codes (column B) — use absolute references ($) so the range doesn't shift when you drag the formula down
  • 2 = the column number in the range where zip codes are stored
  • FALSE = forces an exact match (critical to preserve your mapping accuracy)
• To pull in zones: In cell C2, use a similar formula pointing to your second sheet:
  =VLOOKUP(A2, Sheet2!$A$2:$B$100, 2, FALSE)
• Drag both formulas down to apply them to all Branch codes. If a code doesn't have a match in one of the sheets, you'll get #N/A — wrap the formula in IFERROR to make it cleaner, like:
  =IFERROR(VLOOKUP(A2, Sheet1!$A$2:$B$100, 2, FALSE), "No match")

If you want more flexibility (e.g., your Branch codes aren't the first column in a sheet), this combo is better than VLOOKUP:

• Set up your merged table's Branch codes column as before.
• For zip codes in cell B2:
  =INDEX(Sheet1!$B$2:$B$100, MATCH(A2, Sheet1!$A$2:$A$100, 0))
  Here, INDEX grabs the value from the zip code column, and MATCH finds the row number where the Branch code matches.
• For zones in cell C2:
  =INDEX(Sheet2!$B$2:$B$100, MATCH(A2, Sheet2!$A$2:$A$100, 0))
• Again, drag to fill, and use IFERROR if you want to handle missing matches gracefully.

If you're working with lots of data, or need to refresh the merge regularly when mappings change, Power Query is the most efficient option:

• Go to Data > Get Data > From File > From Excel Workbook (if your mappings are in the same file, you can also import directly from worksheets via From Sheet).
• Import both of your mapping tables into Power Query. Clean them up first (remove blank rows, duplicates, etc.) using the editor's tools.
• Select one of the tables, then click Home > Merge Queries > Merge Queries as New. Choose the other table, select Branch codes as the matching column, and pick a join type:
  • Inner join: Only keeps Branch codes that exist in both tables (great if you want to ensure all codes have both a zip and zone)
  • Full Outer join: Keeps all Branch codes from both tables (even if one is missing a zip or zone)
• After merging, click the expand icon next to the merged table column, and select the column you want to add (zip codes or zone).
• Click Close & Load to send the merged data to a new worksheet. Whenever your source mappings update, just right-click the merged table and select Refresh to update everything automatically.

Quick Tips to Avoid Issues #

• Make sure your Branch codes have consistent formatting across both sheets (e.g., no extra spaces, same text/number type). Use =TRIM(A2) to clean up stray spaces if needed.
• If a sheet has duplicate Branch codes, VLOOKUP/INDEX+MATCH will return the first match, while Power Query lets you choose to keep duplicates or remove them based on your needs.

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