Google 表格公式 - 检索2

在前文中我们介绍了使用 MAP 加 FILTER 代替 VLOOKUP，但是在那种情景中，实际上每次进行过滤的时候，只有一行会被过滤出来，但是理论上 FILTER 应该会检查所有要被过滤的内容的，有可能要比 MATCH 或 VLOOKUP 这种只输出匹配的第一个值的函数要慢一点。所以本文记录一下使用 MATCH 的替代方法。

MATCH

MATCH 函数接受三个参数，第一个参数是要匹配的值，第二个参数是要匹配的范围，这个范围 必须是一维的，也就是说只能是单行或者单列，否则会报错。第三个参数通常为 0（表示范围未排序）。

MATCH 返回的是第一个匹配的值在范围内的 索引。

这里我们先选择 CHOOSEROWS 与 MATCH 配合使用。

一个错误

最开始的思路是使用 MAP 加 MATCH 返回一个要检索的索引数组，然后直接传递给 CHOOSEROWS 来重组每个索引对应的行。但是实际情况下，有时候要检索的值的个数可能并不固定，这样有时候 MATCH 就会尝试检索一个空值，得到的结果就是找不到该值，然后返回一个错误，这个错误会与其他能找到的索引一起组成数组传递给 CHOOSEROWS，但是后者不能处理错误，所以直接整个报错。

修正

对于此错误，暂时也没什么好的处理办法，只能放弃传递索引数组给 CHOOSEROWS 的方法，转换思路。先用 CHOOSEROWS 和 MATCH 选出一行，再使用 MAP 对每一个要检索的值都进行该操作。如果遇到空值，直接排除掉。

如下样表，

	A	B	C	D	E	F	G
1	ID	Name	Age	Sex
2	1	Tom	12	M
3	2	John	43	M
4	2	Alice	45	F
5	3	Dom	23	M
6	4	Tina	34	F

在 G2 中输入公式

=MAP(F2:F6, LAMBDA(x, IF(x="", "", CHOOSEROWS({B2:B6,D2:D6}, MATCH(x, A2:A6, 0)))))，

	A	B	C	D	E	F	G	H
1	ID	Name	Age	Sex		Range	Name	Sex
2	1	Tom	12	M		1	Tom	M
3	2	John	43	M		2	John	M
4	2	Alice	45	F		4	Tina	F
5	3	Dom	23	m
6	4	Tina	34	F

可以看到，MAP 的范围是 F2:F6，虽然 F5 和 F6 是空值，但也不影响检索。

该方式相比于使用 VLOOKUP 同样拥有不限制检索列的位置，以及自动矫正范围列的移动的特性。只不过相比于使用 FILTER 的性能差异，没有去实际测试。

INDEX

除了 CHOOSEROWS，更常与 MATCH 配合使用的是 INDEX。

INDEX 接受三个参数，第一个参数是要索引的范围，第二个参数表示检索第几行，第三个参数表示检索第几列。当第二个参数为 0 时，会返回一整列，当第三个参数为 0 时，会返回一整行，都为 0 时，直接返回整个范围。

所以上例中将 G2 的公式改为

=MAP(F2:F6, LAMBDA(x, IF(x="", "", INDEX({B2:B6,D2:D6}, MATCH(x, A2:A6, 0), 0))))

可以达到相同的效果。可能 INDEX 加 MATCH 组合更常见吧，所以推荐使用 INDEX 而非 CHOOSEROWS。

FILTER 和 MATCH

其实两者的区别也挺明显，当结果会有多行的时候，使用 FILTER；当结果只能是单行的时候，使用 MATCH。