Xlookup还想全面吊打Vlookup？大数据量测试告诉你真相
Xlookup还想全面吊打Vlookup？大数据量测试告诉你真相
微软推出的Xlookup函数自面世以来，就被广泛认为是Vlookup的升级版。但Xlookup真的能在所有场景下超越Vlookup吗？本文通过实测10万行数据的查询速度，对比了Xlookup、Vlookup以及Index/Match等常用函数的性能表现，得出了令人意外的结论。
自从几年前微软推出Xlookup函数以来，网上的画风是这样的。但Xlookup真能全面吊打Vlookup吗？至少在大数据量的实战面前，Xlookup还只是个小弟。
测试方式
- 十万行数据的Excel文件，对每行分别使用Xlookup、Vlookup和Index/Match组合函数；
- 用VBA记录运算时间；
- 运行设备：MacBook Air刷Windows系统，算是比较低端的配置，更贴近实战情况；
- 测试20次，计算平均时间。
Xlookup VS Vlookup
这是最直接的对比，10万行Vlookup平均花了0.4秒，Xlookup平均0.56秒，前者比后者足足快了40%！
Xlookup VS Index/Match
Index/Match组合，是另一个常用的查询函数，有时比vlookup更灵活。测试结果如下。
Index/Match组合平均0.41秒，略慢于Vlookup，但也明显比Xlookup更快（快了36%）。另一个组合方式，Index/XMatch，则比Xlookup还慢10%。这里似乎可以得出一个结论，即近年推出的"X"开头的升级版新函数，运行速度都比原版函数更慢。
Xlookup 的二级制搜索模式
Xlookup比Vlookup多了2个可选参数，其中一个是搜索模式。其中，如果搜索目标列是升序或降序排序，则可以选用二进制搜索模式。在这种情况下，该函数不会马上进行全列搜索，而是先将列按值的大小，一分为二，如果查找值在目标列的上一半，则进一步将上一半的数据一分为二继续查找，依此类推。因为每次都将目标列缩小了一半的范围，因此速度会比全局查找要快得多。实验也得到了验证，采用二进制模式，用时缩短了69%。
Vlookup也有自己的“快速”查找模式，即用模糊匹配取代精确匹配。我们再来看看Xlookup的二进制模式对比Vlookup的模糊匹配。Vlookup模糊匹配比精确匹配提速明显，从0.4秒下降到0.14秒，同时也比Xlookup的二进制模式略快。但优势已经不如Vlookup对比Xlookup常规模式那么明显。
二维匹配
即行和列的值都要匹配，这是VLookup无法实现的。Index/Match需要再加一个Match。对比下来，Index/Match/Match仍然比Xlookup快了近30%。
加上二进制搜索模式之后，Xlookup提速42%，反超Index/Match/Match。
未找到值
Xlookup比Vlookup多了另一个参数是：如未找到有效的匹配值，返回指定值。我们都知道，如果Vlookup查找不到对应值，会返回错误。这时需要外层套一个Iferror。Xlookup这个参数相当于内置了iferror。下图测试了三种情况：Xlookup使用内置的参数，Xlookup嵌套Iferror，Vlookup嵌套Iferror。结果是Xlookup无论用内置还是嵌套的形式，速度差异不大。但都比Vlookup嵌套Iferror慢了一倍以上。
结论
Xlookup操作上Vlookup更灵活，至少它不需要再去数目标列在哪个位置。但通过上述实战对比，我们可以清晰地看到，处理数以万计的数据时，X字辈函数（Xlookup、Xmatch）在它们的老前辈Vlookup、Match面前仍然不够看。全面替代Vlookup？不存在的。