经常碰到一些题目，要求返回某某某第1条数据，第N条数据，最后一条数据，所有数据等等。
下面通过一个简单的栗子，来分享一些方法。
数据源：
第一条数据：（以张三为栗子）
1、vlookup默认返回所有数据的第一条数据。
=VLOOKUP(D1,A1:B11,2,0)
除了vlookup还可以应用match函数
match精确查找，默认返回的也是第一条数据所在的位置（行号）
=INDEX(B:B,MATCH(D1,A1:A11,0))
这两个公式，index match效率会高不少。
最后一条数据：lookup
=LOOKUP(1,0/(A2:A11=D1),B2:B11)
下面就A2:A11=D1这个结构来说说如何构建数组。
A2:A11=D1，遍历A2:A11中每一个单元格，如果单元格是张三，那么就返回true，否则返回false，抹黑A2:A11=D1，按f9得出下列数组。
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
然后，再进一步处理该数组，将其转换为数值形式的数组。
我们要先知道，true和false也是可以用来做运算的。
比如：1 TRUE=2,1 FALSE=1;那么在算是中true=1，false=0（注意，true并不一定等于1，false并不一定等于0，只是在算式中才会这样，有时候只是单纯的逻辑判断结果。）
0/(A2:A11=D1)即：
0/{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
也就是0/1=0,0/0返回#DIV/0!，上述结果为：
{0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!}
再运用lookup函数，查找1，在
{0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!}
中查找，显然是找不到正确的结果。那么lookup函数就会返回比1小的最后一个数字，也就是最后一个0的位置。
.....
所以，这样就找到了张三的最后一条数据。
当然，我们也可以这样来构建数组。
1、A1:A11=D1，构建单元格返回等于“张三”的数组，返回
{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
2、既然我们已经知道True=1，False=0；那么我们可以用行号来做文章。
构建行号一般用ROW函数，如row(B1:B11)或row（1:8）,返回结果
{1;2;3;4;5;6;7;8;9;10;11}，表示1到11行
3、将1、2两个数组相乘
(A1:A11=D1)*ROW(B1:B11)
⬇
{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}*{1;2;3;4;5;6;7;8;9;10;11}
⬇
{0;2;0;0;5;0;0;8;0;0;0}
至此，我们看到，凡是数组中大于0的数字，都是张三所在的行号，我们取最大的数值即可，使用max函数，公式为：（数组公式，需ctrl shift enter三键一起按）
=MAX((A1:A11=D1)*ROW(B1:B11))
由此，得出，张三最后一条数据所在的行号为8，那么返回其最后一条数据的方法就很多了，可以使用index，offset，indirect等等。
=INDEX(B:B,MAX((A1:A11=D1)*ROW(B1:B11)))
=OFFSET(B1,MAX((A1:A11=D1)*ROW(B1:B11))-1,)
=INDIRECT('b'&MAX((A1:A11=D1)*ROW(B1:B11)))
除了用max函数，我们也可以用match函数来构建数组
=MATCH(0,0/(A1:A11=D1),1)，数组三键后，返回结果 8；得出张三的最后一条数据所在的行号。
返回张三的所有数据。
函数：index small if的万金油公式，此公式在这里就不再多说，这是一条很出名的公式，百度一下就很多很多。
函数辅助列：
=COUNTIF(A$2:A2,A2)，相对引用单元格区域，对张三进行计数
添加辅助列之后，要引用那一条数据就用可以引用那一条数据，或者所有数据。
1、所有数据：sumifs
=SUMIFS(B:B,A:A,$D$1,C:C,ROW(A1))
2、所有数据：lookup
=LOOKUP(1,0/(($A$2:$A$11=$D$1)*($C$2:$C$11=ROW(A1))),$B$2:$B$11)
或者改变辅助列的公式为：
=A2&COUNTIF(A$2:A2,A2)
=INDEX(B:B,MATCH(D$1&ROW(A2),$C$1:$C$11,0),)
=LOOKUP(1,0/($C$2:$C$11=$D$1&ROW(A1)),$B$2:$B$11)
辅助列的方法，其实是十分灵活简单有效率的，比单纯的万金油公式在实际运用中要好得多。
用辅助列的方法可以得出任意第N条数据。
辅助列，甚好。
联系客服
