Excel：如何实现区分大小写条件匹配的最大值对应条目查询

方案1：XLOOKUP + FILTER（适配Excel 365/Google Sheets） #

无需辅助列，通过严格大小写匹配筛选后提取最高分对应条目：

Excel 365 公式 #

=XLOOKUP(MAX(FILTER(Sheet1!$B$2:$B$100, EXACT(Sheet1!$C$2:$C$100, "a/a")*EXACT(Sheet1!$D$2:$D$100, "b/b"))), Sheet1!$B$2:$B$100, Sheet1!$A$2:$A$100, "", 0, 1)

Google Sheets 公式 #

=XLOOKUP(MAX(FILTER(Sheet1!$B$2:$B$100, EXACT(Sheet1!$C$2:$C$100, "a/a"), EXACT(Sheet1!$D$2:$D$100, "b/b"))), Sheet1!$B$2:$B$100, Sheet1!$A$2:$A$100, "", 0, 1)

• 核心逻辑：EXACT实现大小写严格匹配，FILTER筛选符合双基因条件的得分，MAX提取最高分，最后XLOOKUP定位对应条目（若有多个同分条目，1参数返回第一个匹配项）。

方案2：INDEX + MATCH + IF数组公式（兼容旧版Excel） #

针对不支持动态数组的旧版Excel，用数组公式实现：

=INDEX(Sheet1!$A$2:$A$100, MATCH(MAX(IF(EXACT(Sheet1!$C$2:$C$100, "a/a")*EXACT(Sheet1!$D$2:$D$100, "b/b"), Sheet1!$B$2:$B$100)), Sheet1!$B$2:$B$100, 0))

• 注意事项：旧版Excel需按Ctrl+Shift+Enter作为数组公式输入，Excel 365直接回车即可。
• 核心逻辑：IF结合EXACT生成仅符合条件的得分数组，MAX取最高分后，通过MATCH+INDEX定位对应条目。

方案3：QUERY函数（Google Sheets专属） #

利用QUERY默认区分大小写的字符串匹配特性：

=INDEX(QUERY(Sheet1!$A$2:$D$100, "SELECT A WHERE C = 'a/a' AND D = 'b/b' ORDER BY B DESC LIMIT 1", 0))

• 核心逻辑：通过SQL风格语句筛选符合基因条件的行，按得分降序排序后取第一条（即最高分条目）。

通用说明 #

所有方案均保留原始基因字符的完整性，通过EXACT或QUERY的默认规则确保大小写严格匹配，完全满足生物学数据准确性要求。若需返回所有同分的符合条件条目，可移除LIMIT 1（Google Sheets）或改用FILTER直接输出数组（Excel/Google Sheets）。

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