吊打xlookup，气晕Filter函数，它竟然是多列查找的NO.1公式

前几天兰色讲了xlookup的多列查找的用法：只需要在第一列设置公式就可以自动查找出后面所有列的值。

=XLOOKUP(H3&I3,A2:A22&B2:B22,C2:E22)

有很多同学随后提问：如果被查找的列不连续怎么办？如下图所示性别、文化程度和基本工资在原表中并不连续。

这.......确实难办！

如果单条件查找可以用Vlookup+match函数组合，但本例需要同时按部门和姓名查找。

如果用xlookup函数呢，感觉应该不难，但写出的公式却意外的复杂：

=XLOOKUP($H5&$I5,$A$2:$A$22&$B$2:$B$22,INDEX($A$2:$F$22,,MATCH(J$4,$1:$1,0)))

公式说明：用match函数查找列数，再用index提取该列做为xlookup的第3个参数

再试试大牛filter函数，公式依然很复杂，也要用index+match动态提取列

=FILTER(INDEX($A$2:$F$22,,MATCH(J$2,$1:$1,0)),$A$2:$A$22&$B$2:$B$22=$H3&$I3)

几个主流的查找函数看来都很复杂，兰色突然想到有一个冷门函数可以自动按行内容查找，它就是Hlookup。看一个简单的例子：

【例】如下图根据年份查找对应的销售成本

=HLOOKUP(A6,$1:$3,3,0)

公式说明：hlookup会在第一行查找A列的年份所在列数，然后返回第3行的值。

了解了hlookup的基本用法，就可以用它写出比xlookup和filter简单的多的公式：

=HLOOKUP(J$4:L$4,A:F,MATCH(H5&I5,A$1:A$22&B$1:B$22,0),0)

注：第1个参数J$4:L$4用多列值，可以让公式只在J例设置，K和L列值可以自动查找

用hlookup之所以简单，有两个原因：

1、hlookup本来就可以按行搜索，而其他函数需要用index、Indirect等函数生成动态列数。

2、本例中hlookup只需要在第1列设置公式，省去了大量的绝对引用符合$

用hlookup还有一个好处，就是各个版本通用，不过非office365需要选取J:L列，按Ctrl+shift+enter三键输入数组公式，输入完成后公式两边会自动添加大括号。

兰色说：在Excel表格中，不要小看任一个函数，微软把它们添加进来，都有特定的用法。