Excel特殊场景下从下至上查找首个非零值的XLOOKUP替代公式需求

问题分析 #

原公式=XLOOKUP(1,M$1:M$6,$A$1:$A$6,"NA",1,-1)不符合需求的原因是：你用1作为查找值，匹配模式1会查找大于等于1的数值，而Column2中从后往前第一个满足这个条件的是1（对应Column1的1.3），但你需要的是任意非零值的首个匹配项，逻辑不匹配。

可行替代公式 #

1. 修正后的XLOOKUP公式 #

直接基于非零条件查找，适配需求：

=XLOOKUP(TRUE,M$1:M$6<>0,$A$1:$A$6,"NA",0,-1)

• 逻辑：M$1:M$6<>0生成布尔数组（非零值对应TRUE），搜索模式-1从后往前定位第一个TRUE，返回对应A列的值。

2. INDEX + MATCH组合公式 #

适合不支持XLOOKUP的旧版Excel：

=INDEX(A$1:A$6,MAX(ROW(M$1:M$6)*(M$1:M$6<>0)))

• 逻辑：ROW(M$1:M$6)*(M$1:M$6<>0)为非零值返回对应行号、零值返回0，用MAX提取最大行号（即最靠下的非零值行），再通过INDEX获取A列对应内容。
• 若使用Excel 365/2021，也可采用动态数组写法：

=INDEX(A$1:A$6,XMATCH(TRUE,M$1:M$6<>0,0,-1))

3. LOOKUP简洁公式 #

利用LOOKUP自动忽略错误值、从后往前匹配的特性：

=LOOKUP(2,1/(M$1:M$6<>0),A$1:A$6)

• 逻辑：1/(M$1:M$6<>0)为非零值生成1、零值生成错误值，LOOKUP会跳过错误值，定位最后一个1对应的A列值。

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