Hey there! Let's work through your data matching task—you need to pull specific fields from Sheet2 and Sheet3 by matching email addresses, right? I'll cover both native spreadsheet formulas (no coding needed) and a custom function option to give you flexibility.

These methods work directly in your spreadsheet without needing to write code.

Method 1: XLOOKUP + ARRAYFORMULA (Fast & Intuitive) #

This lets you batch-process all rows at once, matching emails and pulling the exact fields you need. Paste this into the first cell of a blank sheet (like Sheet4!A2):

=ARRAYFORMULA(
  IFERROR(
    XLOOKUP(Sheet3!E2:E, Sheet2!B2:B, {Sheet3!B2:B, Sheet3!C2:C, Sheet3!E2:E, Sheet2!D2:D, Sheet3!F2:F}, "No Match"),
    "No Match"
  )
)

What this does: #

• XLOOKUP matches every email in Sheet3's email column (E) to Sheet2's EMAIL-ADDRESS column (B)
• Returns the requested fields in order: first_name (Sheet3 B), last_name (Sheet3 C), email (Sheet3 E), PRODUCT (Sheet2 D), amount (Sheet3 F)
• ARRAYFORMULA applies the logic to all rows automatically
• IFERROR handles cases where no matching email is found (replace "No Match" with whatever you prefer)

Method 2: QUERY + VLOOKUP (If you prefer QUERY syntax) #

If you're more comfortable with the QUERY function, use this two-step approach:

1. Add Sheet2's PRODUCT to Sheet3: In a blank column in Sheet3 (e.g., G2), paste:

   =ARRAYFORMULA(VLOOKUP(E2:E, Sheet2!B:D, 3, FALSE))
   

   This pulls the matching PRODUCT from Sheet2 into Sheet3 for every email.
2. Extract the final dataset: In a new sheet, use QUERY to filter and select your fields:

   =QUERY(Sheet3!A:G, "SELECT B, C, E, G, F WHERE G IS NOT NULL", 1)
   

   This selects only rows where a PRODUCT was found (no missing matches) and returns your desired columns.

If you want more control (like case-insensitive matching or custom filters), build a custom function using Google Apps Script:

Step 1: Create the function #

1. Open your spreadsheet, click Extensions > Apps Script
2. Delete the default code and paste this:

function GETMATCHEDDATA() {
  // Get references to your sheets
  const sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  const sheet3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3");
  
  // Pull all data from both sheets
  const sheet2Data = sheet2.getDataRange().getValues();
  const sheet3Data = sheet3.getDataRange().getValues();
  
  // Create a map of emails to products (case-insensitive)
  const emailProductMap = new Map();
  for (let i = 1; i < sheet2Data.length; i++) {
    const email = sheet2Data[i][1].toLowerCase(); // Sheet2 EMAIL-ADDRESS is column B (index 1)
    const product = sheet2Data[i][3]; // Sheet2 PRODUCT is column D (index 3)
    if (email) emailProductMap.set(email, product);
  }
  
  // Prepare results with header row
  const results = [["first_name", "last_name", "email", "PRODUCT", "amount"]];
  
  // Loop through Sheet3 to find matches
  for (let i = 1; i < sheet3Data.length; i++) {
    const firstName = sheet3Data[i][1]; // Sheet3 first_name is column B (index 1)
    const lastName = sheet3Data[i][2]; // Sheet3 last_name is column C (index 2)
    const email = sheet3Data[i][4]; // Sheet3 email is column E (index 4)
    const amount = sheet3Data[i][5]; // Sheet3 amount is column F (index 5)
    
    if (email) {
      const matchedProduct = emailProductMap.get(email.toLowerCase());
      if (matchedProduct) {
        results.push([firstName, lastName, email, matchedProduct, amount]);
      }
    }
  }
  
  return results;
}

Step 2: Use the custom function #

Go back to your spreadsheet, select a blank cell (like Sheet4!A1), and enter:

=GETMATCHEDDATA()

Press enter, and the function will populate all matching rows with your requested fields automatically.

Note on your original formula #

Your initial QUERY formula had a couple of issues:

• It was trying to concatenate columns B & C from Sheet2, but those are NAME and EMAIL-ADDRESS (not first/last name)
• It only worked for a single row (no batch processing)
• It’s prone to errors if emails contain special characters (like quotes)

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