Google Sheets纯数字转换值引发VLOOKUP失效问题及解决

场景说明 #

我创建了名为《Product Test》的Google表格，配置如下：

• A列是带固定前缀的ID字段（示例：ID-KNYT-12345），其中KNYT、DMXF为可变标识
• B列通过自定义函数Convert()处理ID：
  • 若ID前缀为KNYT，仅保留数字部分
  • 若ID前缀为DMXF，保留DMXF-前缀加数字
  • 特定DMXF值直接替换为状态文本（如DMXF-2245替换为Evaluated）
• C列使用VLOOKUP+IMPORTRANGE组合公式，通过B列转换后的值从外部表格匹配获取对应信息

异常情况 #

• 当B列转换结果为**纯数字（如12345）**时，VLOOKUP报错Did not find value in lookup evaluation，匹配失败
• 转换结果为字母数字组合（如DMXF-25452）时，匹配完全正常
• 手动在A列输入纯数字时，VLOOKUP可正常完成匹配

已排查内容 #

• 所有单元格格式设置为Number > AUTOMATIC
• 目标表格中存在对应的匹配值
• 数据无隐藏字符或空格（数据来源于邮件Excel预览后的复制粘贴）

原自定义函数代码 #

function Convert(Thevalue) 
{
  Thevalue = Thevalue.toString().replace("ID-KNYT-", "");
  Thevalue = Thevalue.toString().replace("ID-DMXF-", "DMXF-");

  if (Thevalue == "DMXF-2245"){Thevalue = "Evaluated";}
  if (Thevalue == "DMXF-3268"){Thevalue = "Pending";}

  return Thevalue;
}

所用VLOOKUP示例公式 #

VLOOKUP($B1,IMPORTRANGE("https://docs.google.com/spreadsheets/d/feiugsdfjhsdkjfhiesdfjh-p-dsflkjgsdf/edit#gid=000222333","sheet1!$A:$C"),3,FALSE)

问题根源 #

自定义函数返回的纯数字实际是字符串类型，而目标表格中匹配列的数字为数值类型。VLOOKUP在严格匹配（FALSE）模式下，类型不匹配会直接导致查找失败；手动输入纯数字时，Google Sheets会自动识别为数值类型，因此能正常匹配。

修复方案 #

在自定义函数末尾添加类型判断逻辑，将可转换为数值的字符串转为数值类型后返回：

修复后完整代码 #

function Convert(Thevalue) 
{
  Thevalue = Thevalue.toString().replace("ID-KNYT-", "");
  Thevalue = Thevalue.toString().replace("ID-DMXF-", "DMXF-");

  if (Thevalue == "DMXF-2245"){Thevalue = "Evaluated";}
  if (Thevalue == "DMXF-3268"){Thevalue = "Pending";}

  // 类型转换处理：纯数字字符串转为数值类型
  if (!isNaN(Thevalue))
  {
    return Number(Thevalue);
  }
  else
  {
    return Thevalue;
  }
}

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