如何用Query或VLOOKUP关联两表生成邮箱匹配的指定字段报表？

Got it, let's break this down for you depending on which tool you're using—since you didn't specify, I'll cover the two most common scenarios: SQL (for database systems) and Excel (for spreadsheet workflows).

You'll want to use an INNER JOIN here because it only keeps records where the email matches exactly between both tables. The join condition will target people.email and sales.[EMAIL ADDRESS] (the brackets are needed because the Sales field has a space in its name).

Here's the ready-to-run query:

SELECT 
    p.full_name,
    s.[EMAIL ADDRESS],
    s.PRODUCT,
    p.commission
FROM 
    People p
INNER JOIN 
    Sales s ON p.email = s.[EMAIL ADDRESS];

• INNER JOIN ensures we only get rows where there's a matching email in both tables (no unmatched entries from either side)
• We use aliases p and s for the People and Sales tables to keep the query clean and readable
• The brackets around [EMAIL ADDRESS] are non-negotiable here because the field name contains a space (pro tip: avoid spaces in database field names if you can—they make queries more cumbersome!)

Option 1: Power Query (Recommended for Large Datasets) #

Power Query is the most efficient way to handle this, especially if your tables are big or you need to refresh the report later:

1. Go to the Data tab, and load both the People and Sales tables into Power Query (use "From Table/Range" for each table)
2. In the People query, trim down the columns: right-click and remove everything except full_name, email, and commission
3. In the Sales query, keep only EMAIL ADDRESS and PRODUCT columns
4. Go back to the People query, click Merge Queries > Merge Queries as New
5. In the merge window:
   • Select the email column from the People table
   • Select the EMAIL ADDRESS column from the Sales table
   • Choose Inner join as the join type
6. Expand the merged Sales table, and uncheck all columns except PRODUCT
7. Rename the email column to EMAIL ADDRESS if you want consistency, then load the final result back to Excel as a new table

Option 2: XLOOKUP/VLOOKUP (For Smaller Datasets) #

If you prefer using spreadsheet functions, here's how to do it:

Using XLOOKUP (Excel 365/2021+) #

Assume your People table is in Sheet1 and Sales in Sheet2:

• Create a new sheet with headers: full_name, EMAIL ADDRESS, PRODUCT, commission
• For full_name (cell A2):

  =XLOOKUP(B2, Sheet1!$K:$K, Sheet1!$D:$D, "No Match", 0)
  

• For commission (cell D2):

  =XLOOKUP(B2, Sheet1!$K:$K, Sheet1!$R:$R, "No Match", 0)
  

• Finally, filter out any rows showing "No Match" to keep only valid matching records

Using VLOOKUP (Older Excel Versions) #

VLOOKUP requires the lookup column to be the first in your range, so you may need to rearrange the People table or use a helper column:

=VLOOKUP(B2, Sheet1!$K:$R, 8, FALSE) // Pulls full_name (adjust the column number if your table layout differs)
=VLOOKUP(B2, Sheet1!$K:$R, 13, FALSE) // Pulls commission

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