如何用Excel LET函数优化文本单元格数值范围查找及价格匹配

核心思路 #

通过LET函数封装变量，先从商品描述文本中提取16-20范围内的数字作为匹配尺寸，再用XLOOKUP关联另一工作簿的价格数据，让公式逻辑更清晰、易维护。

具体公式示例 #

=LET(
    desc, A2,
    // 提取描述中的所有连续数字（处理"XX18XX"这类文本）
    raw_num, TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(desc, ROW(INDIRECT("1:"&LEN(desc))), 1)), MID(desc, ROW(INDIRECT("1:"&LEN(desc))), 1), "")),
    // 筛选16-20范围内的有效尺寸，不符合则返回错误值
    valid_size, IF(AND(--raw_num >= 16, --raw_num <= 20), --raw_num, NA()),
    // 匹配另一工作簿的价格，无匹配时返回自定义提示
    match_price, XLOOKUP(valid_size, '[价格表.xlsx]Sheet1'!$B:$B, '[价格表.xlsx]Sheet1'!$C:$C, "无对应价格", 0),
    match_price
)

变量说明 #

• desc：指定商品描述的单元格引用（示例为A2），后续可直接修改，无需重复调整公式多处
• raw_num：提取描述文本中的所有数字字符，拼接为完整数字字符串
• valid_size：将提取的数字转为数值后，判断是否在16-20区间内，符合条件则保留，否则返回#N/A
• match_price：以有效尺寸为匹配键，关联另一工作簿的「Product Size」列和价格列，返回对应价格

特殊场景调整 #

如果商品描述中存在多组数字（比如"16-20XX"），可改用以下逻辑提取所有符合范围的两位数：

=LET(
    desc, A2,
    // 提取文本中所有连续两位数，筛选16-20的结果
    valid_sizes, FILTER(--MID(desc, SEQUENCE(LEN(desc)-1), 2), AND(--MID(desc, SEQUENCE(LEN(desc)-1), 2)>=16, --MID(desc, SEQUENCE(LEN(desc)-1), 2)<=20)),
    // 取第一个有效尺寸匹配价格（按需可改为TEXTJOIN返回多结果）
    match_price, XLOOKUP(INDEX(valid_sizes,1), '[价格表.xlsx]Sheet1'!$B:$B, '[价格表.xlsx]Sheet1'!$C:$C, "无对应价格", 0),
    match_price
)

注意事项 #

• 替换公式中的[价格表.xlsx]Sheet1为实际的工作簿文件名和工作表名称
• 若商品描述中的尺寸存在非连续数字（如"1X8"），需额外添加字符清理逻辑，可结合SUBSTITUTE去除非数字字符后再提取

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