Question

Excel2013，使用OFFSET引用某单元格下第7行数据时，这个公式为什么不能执行？单独执行时没错。

=OFFSET((ADDRESS(MATCH((vlookup(A342,C$5:C$256,1,)),C:C,),1,4)),7,)

我想表达的是：当A342的值和C列的某个单元格相同时，则取该单元格下面的第7行单元格的值。
单独执行=ADDRESS(MATCH((vlookup(A342,C$5:C$256,1,)),C:C,),1,4)没问题，可以得出单元格位置，外面再用OFFSET就不行了。请教大神指点迷津~~

Answer 1

ADDRESS得到的是单元格地址，是一个文本字符串，要再用INDIRECT才能转换为单元格引用。

OFFSET函数第1参数类型必须是单元格引用。因此出现问题。

 

【分析】

你这个公式设计的很复杂：

1. ADDRESS的第1参数是match（返回数字n），第2参数是1，第3参数是4，也就是说返回的是An单元格。再用OFFSET将An单元格偏移7行。也就是n+7。

2. VLOOKUP的第3参数是1，第4参数是逗号，也就是精确查找。除非A342用了*或?通配符，否则查找出来的还是A342自身，如果没有该值，则是#N/A!错误。——那么，问题来了，直接用A342就可以了，不需要再VLOOKUP。

 

【简化】加一个判断，避免返回#N/A错误，公式match后面+8是因为OFFSET(A1,7)引用A8，相当于INDEX(A:A,8)，最终给出公式如下：

=IF(COUNTIF(C:C,A342),INDEX(A:A,MATCH(A342,C:C,0)+8),"")

追问

不好意思，我ADDRESS的第2参数应该是3，得出的结果是C94，如果是1就是A94，我想要的结果是如果A342是C列第94行的值，那么我就取C101.
如果这样，是不是可以把你的公式改为=IF(COUNTIF(C:C,A342),INDEX(C:C,MATCH(A342,C:C,0)+8),"")

追答

是的。如果你是要返回C101的话，MATCH后面应该+7，而不是+8。
有的教程喜欢把简单的事情弄复杂了，呵呵。

追问

多谢，不过你这个公式报了循环警告，结果也不正确。。。楼下那个兄弟的对了，虽然我还不知道那个公式什么意思。不过分分真能给1个人~~非常感谢您的解说~~

追答

你公式放在C列？如果是
=IF(COUNTIF(C:C,A342),INDEX(C:C,MATCH(A342,C:C,0)+8),"")
MATCH的第2参数就不能用整列C列。

Answer 2

ADDRESS得到的是引用地址，并不是真正的单元格
因此要使用INDIRECT函数，或者其他函数，得到真正的单元格。

可以改写为：
=INDEX(C:C,MATCH(A342,C$5:C$256,0)+7)

追问

你这个靠谱，不过貌似应该是+11，不是+7，+7得出的结果不对，11才对。

追答

那你改一下
=INDEX(C:C,MATCH(A342,C$5:C$256,0)+11)

Answer 3

改成试试呢
=OFFSET(INDIRECT(ADDRESS(MATCH((VLOOKUP(A342,C$5:C$256,1,)),C:C,),3,4)),7,)

Answer 4

=INDEX(C:C,MATCH(A342,C$5:C$256,0)+11)

追问

这个结果是对的，可以告诉我+11是什么意思吗？

追答

你是从第5行（C5）开始查的，INDEX的第一个参数，是从第一行开始的，5-1+7