在Google Apps Script中实现含多IF判断的Vlookup功能

我已经实现了一个仅支持精确匹配的VLOOKUP脚本，但主表的产品编码（SKU）存在三种变体，无法与数据源直接匹配，需要按规则处理后再匹配：

• 常规SKU：不以“S”开头且不含“@”，直接使用原SKU匹配
• 复古款SKU：包含“@年份”后缀，需移除“@”及后面的内容后再匹配
• 拆分款SKU：以“S”开头，需移除前缀“S”后匹配成本值，再除以对应的拆分单位数

原数组公式 #

=ARRAYFORMULA(IFERROR(IF(ROW(A1:A)=1,"Cost",
IF((REGEXMATCH(A1:A,"@")+(LEFT(A1:A,1)="S"))=0,
VLOOKUP(A1:A,{Imp_Epl!B:B,Imp_Epl!F:F},2,FALSE),
IF(LEFT(A1:A,1)="S", 
VLOOKUP(RIGHT(A1:A, LEN(A1:A)-1),{Imp_Epl!B:B,Imp_Epl!F:F},2,FALSE)/VLOOKUP(RIGHT(A1:A, LEN(A1:A)-1),{Imp_Epl!B:B,Imp_Epl!D:D},2,FALSE),
IF(REGEXMATCH(A1:A, "@"), 
VLOOKUP( LEFT(A1:A, SEARCH("@",A1:A) -1) ,{Imp_Epl!B:B,Imp_Epl!F:F},2,FALSE))
))))))

现有精确匹配脚本 #

function vlookupalternative() {
const dstwb = SpreadsheetApp.getActiveSpreadsheet();              // 目标工作簿
const srcwb = SpreadsheetApp.openById("152Rexxxxxxxxxx");         // 数据源工作簿ID
const dstsheet = dstwb.getSheetByName("COST_SHEET");       // 目标工作表
const srcsheet = srcwb.getSheetByName("PRODUCTS_SHEET");    // 数据源工作表

const srcdata = srcsheet.getRange(2,2,srcsheet.getLastRow()-1,5).getValues()
const searchValues = dstsheet.getRange(2,1,dstsheet.getLastRow()-1,1).getValues()  

const dstheader = dstsheet.getRange(1,1,1,dstsheet.getLastColumn()).getValues()[0].indexOf("PutDatahere")+1; 

const matchSku = searchValues.map(searchRow => {
const matchRow = srcdata.find(r => r[0] == searchRow[0])
return matchRow ? [matchRow[4]] : [null]      // 要获取的目标列
})

dstsheet.getRange(2,dstheader,dstsheet.getLastRow()-1,1).setValues(matchSku)   
}

改进后的脚本（支持三种SKU规则） #

function vlookupWithSkuRules() {
  const dstwb = SpreadsheetApp.getActiveSpreadsheet();
  const srcwb = SpreadsheetApp.openById("152Rexxxxxxxxxx");
  const dstsheet = dstwb.getSheetByName("COST_SHEET");
  const srcsheet = srcwb.getSheetByName("PRODUCTS_SHEET");

  // 读取数据源：B列(SKU)、D列(拆分单位数)、F列(成本)，对应索引0、2、4
  const srcdata = srcsheet.getRange(2, 2, srcsheet.getLastRow() - 1, 5).getValues();
  const searchValues = dstsheet.getRange(2, 1, dstsheet.getLastRow() - 1, 1).getValues();
  
  const dstheader = dstsheet.getRange(1, 1, 1, dstsheet.getLastColumn()).getValues()[0].indexOf("PutDatahere") + 1;

  const matchResults = searchValues.map(searchRow => {
    let sku = searchRow[0];
    if (!sku) return [null]; // 空值直接返回null

    let processedSku = sku;
    let isSplitSku = false;

    // 处理拆分款SKU（以S开头）
    if (sku.startsWith("S")) {
      processedSku = sku.slice(1);
      isSplitSku = true;
    } 
    // 处理复古款SKU（含@）
    else if (sku.includes("@")) {
      processedSku = sku.split("@")[0];
    }

    // 在数据源中查找处理后的SKU
    const matchRow = srcdata.find(r => r[0] === processedSku);
    if (!matchRow) return [null];

    // 根据SKU类型计算结果
    if (isSplitSku) {
      // 拆分款：成本 / 拆分单位数，处理除数为0的情况
      const cost = matchRow[4];
      const splitQty = matchRow[2];
      return splitQty !== 0 ? [cost / splitQty] : [null];
    } else {
      // 常规或复古款：直接返回成本
      return [matchRow[4]];
    }
  });

  dstsheet.getRange(2, dstheader, matchResults.length, 1).setValues(matchResults);
}

脚本说明 #

1. 先对每个SKU进行预处理：根据开头是否为"S"、是否包含"@"，生成用于匹配的标准化SKU
2. 拆分款SKU匹配后，额外获取拆分单位数，计算成本除以单位数的结果，同时处理除数为0的异常
3. 空SKU或匹配不到的情况返回null，对应原公式的IFERROR逻辑

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