Excel跨工作表Lookup操作：通过产品代码填充自定义产品名称

Got it, let's walk through exactly how to use Excel's lookup functions to fill in your custom product names from OurSheet into ManSheet's「Our Name」column. This is a super common task, and I'll cover a few reliable methods depending on your Excel version.

First, let's recap your setup to make sure we're aligned:

• ManSheet: Has columns for unique product codes (format ABC1234), manufacturer names, and the empty「Our Name」column we need to fill.
• OurSheet: Has columns for the same product codes and your custom product names. Both sheets are in the same workbook.

This is the most widely compatible option, great if you're using an older Excel version.

1. Switch to the ManSheet tab.
2. Click the first empty cell in the「Our Name」column (e.g., if your product codes are in column A and「Our Name」is column C, start at cell C2).
3. Paste this formula:

   =VLOOKUP(A2, OurSheet!$A:$B, 2, FALSE)
   

4. Press Enter, then click the bottom-right corner of the cell and drag down to fill the entire column.

Breakdown of the formula: #

• A2: The product code in the current row of ManSheet that we want to match.
• OurSheet!$A:$B: The range in OurSheet where we'll search—column A has the product codes, column B has your custom names. The $ signs lock the range so it doesn't shift when you drag the formula down.
• 2: Tells Excel to return the value from the 2nd column of the lookup range (your custom name).
• FALSE: Ensures we get an exact match (critical since your product codes are unique).

If you have a newer Excel version, XLOOKUP is more intuitive and avoids some of VLOOKUP's limitations.

1. In the first empty cell of「Our Name」in ManSheet, enter:

   =XLOOKUP(A2, OurSheet!$A:$A, OurSheet!$B:$B, "No Match Found")
   

2. Drag down to fill the column.

Breakdown: #

• A2: The product code to match.
• OurSheet!$A:$A: The column in OurSheet with product codes to search through.
• OurSheet!$B:$B: The column in OurSheet with custom names to return.
• "No Match Found": Optional—replaces the default #N/A error with a friendly message if a product code isn't in OurSheet. Omit this if you prefer to see #N/A for missing codes.

This is a favorite among power users because it's more flexible than VLOOKUP (e.g., you can return values from columns to the left of your search column if needed).

1. In ManSheet's「Our Name」column first empty cell, use:

   =INDEX(OurSheet!$B:$B, MATCH(A2, OurSheet!$A:$A, 0))
   

2. Drag down to fill the column.

Breakdown: #

• MATCH(A2, OurSheet!$A:$A, 0): Finds the row number in OurSheet where the product code matches A2. The 0 forces an exact match.
• INDEX(OurSheet!$B:$B, [row number]): Returns the value from that row in OurSheet's custom name column.

• Check for matching formats: Make sure product codes in both sheets are identical—no extra spaces, uppercase/lowercase mismatches, or hidden characters. If you have case issues, wrap the lookup values in UPPER() like this:

  =VLOOKUP(UPPER(A2), UPPER(OurSheet!$A:$B), 2, FALSE)
  

• Handle errors gracefully: If some product codes in ManSheet aren't in OurSheet, wrap your formula in IFERROR() to replace #N/A with a custom message:

  =IFERROR(VLOOKUP(A2, OurSheet!$A:$B, 2, FALSE), "Not in Our List")
  

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