如何用MAKEARRAY/LAMBDA替代下拉公式实现菜品对应国家匹配？

1. 修复MAKEARRAY/LAMBDA公式 #

原公式核心问题是未逐行处理G列菜品：Δ是整个G2:G数组，XMATCH和BYCOL的匹配逻辑没有针对每行单独计算，导致Σ无法返回对应每行的匹配列索引，同时MAKEARRAY中也未引用当前行的菜品。

修复后的公式如下：

LET(
    Γ, B$2:E,
    Δ, G2:G,
    row_count, COUNTA(Δ),
    MAKEARRAY(row_count, 1, LAMBDA(r, c,
        LET(
            current_dish, INDEX(Δ, r),
            IF(LEN(current_dish) = 0, "",
                LET(
                    match_col, XMATCH(TRUE, BYCOL(Γ, LAMBDA(col, REGEXMATCH(CONCAT(col), current_dish)))),
                    INDEX(Γ, 1, match_col)
                )
            )
        )
    ))
)

关键修改点：

• 在MAKEARRAY的LAMBDA(r,c)中，用INDEX(Δ, r)取出当前行的菜品
• 针对当前菜品单独计算match_col，确保每行匹配对应的国家列
• 空值处理更严谨，判断当前菜品长度为0时返回空字符串

2. BYCOL->REGEXMATCH->CONCATENATE是否为最优解？ #

不是最优解，存在两个明显问题：

1. 误匹配风险：REGEXMATCH(CONCAT(col), current_dish)会匹配子串，比如菜品"汉堡"会被包含"牛肉汉堡"的列误匹配
2. 效率较低：拼接整列字符串再做正则匹配，比直接在列中查找的运算成本更高

推荐两种更优的替代方案：

方案1：精准匹配（无歧义） #

用ISNUMBER(XMATCH())替代正则匹配，直接检查列中是否存在当前菜品，避免子串误匹配：

BYROW(G2:G, LAMBDA(dish,
    IF(LEN(dish) = 0, "",
        INDEX(B2:E2, XMATCH(TRUE, BYCOL(B3:E, LAMBDA(col, ISNUMBER(XMATCH(dish, col))))))
    )
))

方案2：扁平化数据查找（最简洁高效） #

用TOCOL将国家和菜品分别扁平化，再用XLOOKUP直接匹配，逻辑直观且扩展性强：

LET(
    countries, TOCOL(B2:E2, 1),
    dishes, TOCOL(B3:E, 1),
    XLOOKUP(G2:G, dishes, countries, "", 0)
)

这个方案无需嵌套复杂的LAMBDA，即使国家列或菜品行扩展，公式也能自动适配。

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