在Excel中匹配文本后查找最大字母数字值的方法

需求说明 #

在A列中匹配C列的文本内容，提取对应B列的字母数字值；当同一文本有多个匹配结果时，返回其中最大的字母数字值（例如INC0012345大于INC00123，且此类值无固定字符长度）。

方法一：适用于Excel 365/2021（动态数组版本） #

直接使用动态数组公式，无需按组合键，公式会自动适配整列：
在结果列（比如D2）输入以下公式，回车后自动填充所有结果：

=TAKE(SORTBY(FILTER(B$2:B$100,A$2:A$100=C2),FILTER(B$2:B$100,A$2:A$100=C2),-1),1)

公式解释：

1. FILTER(B$2:B$100,A$2:A$100=C2)：筛选出A列等于当前C列单元格值的所有B列数据
2. SORTBY(..., ..., -1)：将筛选出的B列数据按降序排序（-1代表降序）
3. TAKE(...,1)：提取排序后的第一个值，也就是最大的字母数字值

方法二：适用于旧版Excel（非365/2021） #

需要使用数组公式（输入后按Ctrl+Shift+Enter确认，而非单独回车），以D2为例：

=INDEX(B$2:B$100,MATCH(MAX(IF(A$2:A$100=C2,VALUE(RIGHT(B$2:B$100,LEN(B$2:B$100)-FIND("INC",B$2:B$100)-2)))),VALUE(RIGHT(B$2:B$100,LEN(B$2:B$100)-FIND("INC",B$2:B$100)-2)),0))

注意事项：

• 公式中的"INC"需要替换为你实际字母数字值的固定前缀（如果前缀不固定，优先用方法一）
• 输入公式后必须按Ctrl+Shift+Enter，Excel会自动在公式前后添加大括号{}，代表数组公式生效

公式解释：

1. RIGHT(B$2:B$100,LEN(B$2:B$100)-FIND("INC",B$2:B$100)-2)：提取B列值中INC之后的数字部分
2. VALUE(...)：将提取的数字文本转为数值，方便用MAX找最大值
3. MAX(IF(A$2:A$100=C2,...))：找到当前C列文本对应的最大数字值
4. INDEX+MATCH：根据最大数字值反向找到对应的完整字母数字值

补充说明 #

如果你的字母数字值无需提取数字，直接通过文本排序就能得到正确大小（比如INC0012345作为文本比INC00123大），旧版Excel也可以用简化的数组公式：

=INDEX(B$2:B$100,MATCH(MAX(IF(A$2:A$100=C2,CODE(RIGHT(B$2:B$100,1))*10^LEN(B$2:B$100)+VALUE(B$2:B$100))),IF(A$2:A$100=C2,CODE(RIGHT(B$2:B$100,1))*10^LEN(B$2:B$100)+VALUE(B$2:B$100)),0))

输入后同样按Ctrl+Shift+Enter确认。

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