如何用Excel单行函数实现类VLOOKUP的重复值最高频次值匹配？

表A中存在A、C这类重复标识值，表B需以自身的A、B、C等标识为输入，从表A中获取对应出现频次最高的值（示例中A对应12，因其出现3次多于11的出现次数；B对应33；C对应14）。表B可能包含D这类表A中没有的标识，也可能缺失A、B、C中的任意一个。已知通过COUNT函数统计后排序可实现需求，但希望找到类似VLOOKUP的单行函数来完成该操作，是否存在这样的Excel函数？

解决方案 #

1. Excel 365/2021 版本（支持动态数组） #

可以用嵌套函数实现单行公式，直接返回结果：

=IFERROR(MODE.SNGL(FILTER(表A!$B:$B,表A!$A:$A=表B!A2)),"无匹配")

• 逻辑：FILTER筛选表A中与表B当前标识匹配的所有值，MODE.SNGL提取其中出现频率最高的数值；IFERROR处理表B中存在表A无匹配标识（如D）的情况，返回自定义提示。

2. 旧版本Excel（不支持动态数组） #

使用数组公式（输入后按Ctrl+Shift+Enter确认），单行完成需求：

=IFERROR(INDEX(表A!$B:$B,MATCH(MAX(COUNTIFS(表A!$A:$A,表B!A2,表A!$B:$B,表A!$B:$B)),COUNTIFS(表A!$A:$A,表B!A2,表A!$B:$B,表A!$B:$B),0)),"无匹配")

• 逻辑：COUNTIFS统计表A对应标识下每个值的出现次数，MAX定位最高频次，MATCH+INDEX找到并返回对应数值；IFERROR处理无匹配场景。

补充说明 #

• 若存在多个值出现频次相同且均为最高的情况，上述公式会返回第一个出现的目标值。
• 若需处理文本类型的匹配值，可将MODE.SNGL替换为INDEX(MODE.MULT(...),1)，或调整旧版本公式的统计逻辑。

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