使用INDEX和MATCH进行Excel双向查找时出现#N/A错误求助

问题根源 #

第一个MATCH函数返回#N/A的核心原因：

• MATCH函数默认仅支持在单行或单列中查找目标值，你当前公式中第一个MATCH的查找区域是Sheet1!A2:L16（整个二维数据区域），不符合函数的查找规则，因此无法定位匹配值。
• 第二个MATCH的查找区域是单行Sheet1!A1:L1，符合函数要求，所以能正常运行。

修正方案 #

根据你的需求（匹配指定列：B2:B16、C2:C16、F2:F16、G2:G16、J2:J16），针对不同Excel版本提供两种公式：

方案1：适用于Excel 365/2021及以上版本 #

利用CHOOSECOLS提取目标列，再用TOCOL转为单列供MATCH查找：

=INDEX(Sheet1!A2:L16, MATCH(Sheet1!B19, TOCOL(CHOOSECOLS(Sheet1!A2:L16,2,3,6,7,10)),0), MATCH(Sheet1!A19,Sheet1!A1:L1,0))

• CHOOSECOLS(Sheet1!A2:L16,2,3,6,7,10)：提取第2、3、6、7、10列（对应B、C、F、G、J列）
• TOCOL(...)：将提取的多列数据转为单列，满足MATCH的查找要求

方案2：适用于旧版Excel（无CHOOSECOLS/TOCOL） #

使用数组公式构建匹配逻辑，旧版需按Ctrl+Shift+Enter确认（新版Excel可直接回车）：

=INDEX(Sheet1!A2:L16, SMALL(IF((Sheet1!B2:B16=Sheet1!B19)+(Sheet1!C2:C16=Sheet1!B19)+(Sheet1!F2:F16=Sheet1!B19)+(Sheet1!G2:G16=Sheet1!B19)+(Sheet1!J2:J16=Sheet1!B19), ROW(Sheet1!A2:A16)-ROW(Sheet1!A2)+1),1), MATCH(Sheet1!A19,Sheet1!A1:L1,0))

• IF(...)：判断指定列中是否存在与B19匹配的值，返回对应行的相对行号
• SMALL(...,1)：取第一个匹配的行号，确保返回正确的行位置

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