求无需指定列、查找同一行两字符串并返回行号的Excel公式

适用于Excel 365/2021（支持动态数组） #

如果你的Excel版本支持动态数组函数，可以用以下公式，无需指定列就能判断两个字符串是否在同一行，并返回第一个匹配的行号（未找到返回9999）：

=IFERROR(ROW(XLOOKUP(1, BYROW('Sheet2'!3:100, LAMBDA(row, AND(COUNTIF(row, Sheet1!B5)>0, COUNTIF(row, Sheet1!C5)>0))), 'Sheet2'!3:100)), 9999)

公式说明：

• BYROW('Sheet2'!3:100, LAMBDA(row, ...))：遍历Sheet2的3到100行，对每一行执行后续判断
• AND(COUNTIF(row, Sheet1!B5)>0, COUNTIF(row, Sheet1!C5)>0)：检查当前行是否同时包含Sheet1!B5和Sheet1!C5两个字符串（COUNTIF统计出现次数>0即存在）
• XLOOKUP(1, ..., 'Sheet2'!3:100)：找到第一个满足条件的行（布尔值TRUE对应1）
• ROW(...)：提取该行的行号
• IFERROR(..., 9999)：如果没有匹配行，返回9999

适用于旧版Excel（不支持动态数组） #

如果是旧版Excel，需要使用数组公式（输入后按Ctrl+Shift+Enter确认）：

=IFERROR(INDEX(ROW('Sheet2'!3:100), MATCH(1, MMULT(--(COUNTIF(OFFSET('Sheet2'!$A$3:$ZZ$100, ROW('Sheet2'!3:100)-ROW('Sheet2'!$A$3), 0, 1, COLUMNS('Sheet2'!$A$3:$ZZ$100)), Sheet1!B5)>0), --(COUNTIF(OFFSET('Sheet2'!$A$3:$ZZ$100, ROW('Sheet2'!3:100)-ROW('Sheet2'!$A$3), 0, 1, COLUMNS('Sheet2'!$A$3:$ZZ$100)), Sheet1!C5)>0)), 0)), 9999)

公式说明：

• OFFSET(...)：逐行提取Sheet2的3到100行数据（每行作为单独区域）
• COUNTIF(..., Sheet1!B5)>0：判断该行是否包含第一个字符串，--将布尔值转为1/0
• MMULT(...)：计算每行两个字符串都存在的结果（两个1相乘得1，代表满足条件）
• MATCH(1, ..., 0)：找到第一个满足条件的行位置，INDEX(ROW(...), ...)提取对应的行号
• IFERROR(..., 9999)：无匹配时返回9999

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