用函数实现筛选的功能，HLOOKUP也来凑热闹

这几天一直说怎么用函数实现筛选的功能，也可以说是一对多的查询问题。我把最近我能想到的方法都来说一下，今天就说下用hlookup函数来实现这个功能。

-01-

具体应用

1.根据姓名查询出所有的记录

还是之前那个案列，只不过今天用hlookup函数来完成。那么思路就会和前面有所变化。

首先说一下大概的思路，用hlookup在B1:E11这个区域中查询G3，也就是蓝框标记的区域。然后返回H1在B1:E11这个区域中的行号，比如刘卓在B1:E11中的行号分别是3，7，9。第一次返回第3行，下拉返回第7行，再下拉返回第9行，这样就把所有的记录查询出来。设置好引用，就可以右拉了。

第一步还是添加辅助列，辅助列的作用是让B列的姓名和H1的姓名进行比较，相等的话返回B列对应的行号。在A2单元格中输入公式=IF(B2=H$1,ROW(2:2))，下拉。这样就得到了要查找名字的行号，也就是hlookup的第3参数。

但是现在还有一个问题，就是对应的行号有多个，每次我们只要取一个行号，就要用到small这个函数，small是返回数组中第k个最小值。比如现在对应的行号是3，7，9。第一次用small返回第1个最小值3，第2次返回第2个最小值7，第3次返回第3个最小值9。也就是SMALL($A$2:$A$11,ROW(1:1))作为hlookup函数的第3参数。

在G4单元格中输入完整的公式=HLOOKUP(G$3,$B$1:$E$11,SMALL($A$2:$A$11,ROW(1:1)),)，下拉发现会出现错误值，用iferror处理。最后的公式为=IFERROR(HLOOKUP(G$3,$B$1:$E$11,SMALL($A$2:$A$11,ROW(1:1)),),"")。

如果不用辅助列就是下面的公式，在G18单元格输入公式=IFERROR(HLOOKUP(G$17,$A$15:$D$25,SMALL(IF($A$16:$A$25=$H$15,ROW($16:$25)),ROW(1:1))-14,),"")，按ctrl+shift+enter三键。

IF($A$16:$A$25=$H$15,ROW($16:$25))这部分是将A列的姓名和H15的姓名进行比较，相等的返回A列对应的行号。也就是代替上面的辅助列。

SMALL(IF($A$16:$A$25=$H$15,ROW($16:$25)),ROW(1:1))这部分是从多个行号中返回第1个行号，第2个行号，。。。

SMALL(IF($A$16:$A$25=$H$15,ROW($16:$25)),ROW(1:1))-14这部分后面减去14是为了让返回的行号和hlookup查找区域的对应姓名的行号一样。比如上图第一个宝玉返回的行号是18，但宝玉在$A$15:$D$25中是第4行。所以要减去14。

剩下的就是hlookup的正常用法，最外层用iferror处理错误值。

链接：

https://pan.baidu.com/s/1DM9FGiuA9zhd938oqnrrZg

提取码：kkqa

你是否看见别人准时下班，心里很羡慕，但依然得埋头继续加班做表格；你是否常常在群里求助别人帮你解决问题，但又看不懂公式；你是否想要免费学习函数却又找不到系统的教程。扫码关注，系统讲解excel常用函数，一条公式让你从重复性工作中解脱出来。也希望你能分享转发给你的朋友。

关注解锁更多函数的用法

点击文末的写留言可以提出你的建议，如果有函数问题要提问，可以加我的微信15003417692，备注excel函数。

点击写留言