欢迎转发和点一下“在看”，文末留言互动!
置顶公众号或设为星标及时接收更新不迷路
小伙伴们好，今天来和大家分享一道查找题目。原题目是下面这样的：
题目要求以家庭为范围，为每一个户主查找配偶。若没有配偶则返回“无”。
看到这个题目后，一时间我也觉得我从下手。观察一下源数据，我们发现，在“关系”一列中“户主”和“配偶”的位置是随机的，而且最后两户且仅有这两户中还没有“配偶”。该如何定位呢？
但仔细想想，由于仅仅最后两户中没有“配偶”，这其实也给我们提供了解题思路了。
利用万金油公式可以分别从源数据中提取到“户主”和“配偶”的信息，再将他们按照行号从小到大排列，之后将两组新的数据组合成一组新数据，使得“户主”和“配偶”一一对应，再用VLOOKUP函数抓取就可以了。
上面就是解决这道题目的思路。
01
IF({1}和IF({1,0})技巧的实例应用
在单元格G2中输入下列公式，三键确认并向下拖曳即可。
=IFERROR(VLOOKUP(F3,IF({1,0},INDEX($B$2:$B$32,N(IF({1},SMALL(IF($C$2:$C$32="户主",ROW($A$2:$A$32)),ROW($1:$31))))-1),INDEX($B$2:$B$32,N(IF({1},SMALL(IF($C$2:$C$32="配偶",ROW($A$2:$A$32)),ROW($1:$31))))-1)),2,0),"无")在这条公式中，我们应用了INDEX函数的一则不为人所熟知的技巧。
IF($C$2:$C$32="户主",ROW($A$2:$A$32))判断哪些是户主，并返回对应的行号。
SMALL(IF($C$2:$C$32="户主",ROW($A$2:$A$32)),ROW($1:$31))利用SMALL函数，将上一步的结果从小到大排列。这一步的结果是{4;7;10;13;19;20;23;26;28;32;#NUM!;...;#NUM!}。
INDEX($B$2:$B$32,N(IF({1},SMALL(IF($C$2:$C$32="户主",ROW($A$2:$A$32)),ROW($1:$31))))-1)接下来重点来了。利用INDEX函数返回对应的户主姓名。这部分的结果是{"刘定金";"龚道兵";"张显玉";"胡昌权";"向立清";"向得华";"吴成友";"向正平";"李巧莲";"袁志军";#NUM!;...;#NUM!}。
这里INDEX函数的第二参数是数组形式。如果INDEX函数的第二参数是数组，则必须使用N(IF({1},))这个套路，否则就会出错。
INDEX($B$2:$B$32,N(IF({1},SMALL(IF($C$2:$C$32="配偶",ROW($A$2:$A$32)),ROW($1:$31))))-1)相同的过程，我们也把“配偶”的姓名提取出来。结果如下：
{"吴康菊";"付元青";"王正连";"程胜莲";"王国芳";"卢自凤";"廖文菊";"刘代香";#NUM!;...;#NUM!}。
IF({1,0},INDEX($B$2:$B$32,N(IF({1},SMALL(IF($C$2:$C$32="户主",ROW($A$2:$A$32)),ROW($1:$31))))-1),INDEX($B$2:$B$32,N(IF({1},SMALL(IF($C$2:$C$32="配偶",ROW($A$2:$A$32)),ROW($1:$31))))-1))接下来又一个重点来了，利用IF函数将上两步的结果合并，组成一个新的内存数组。在这个内存数组中“户主”和“配偶”是一一对应的。
这部分返回的结果是：
{"刘定金","吴康菊";"龚道兵","付元青";"张显玉","王正连";"胡昌权","程胜莲";"向立清","王国芳";"向得华","卢自凤";"吴成友","廖文菊";"向正平","刘代香";"李巧莲",#NUM!;"袁志军",#NUM!;#NUM!,...;#NUM!,#NUM!}。
如果想将两组大小相同的数组合并在一起，就可以使用IF({1},数组1,数组2)这样的结构。
IFERROR(VLOOKUP(F3,IF({1,0},INDEX($B$2:$B$32,N(IF({1},SMALL(IF($C$2:$C$32="户主",ROW($A$2:$A$32)),ROW($1:$31))))-1),INDEX($B$2:$B$32,N(IF({1},SMALL(IF($C$2:$C$32="配偶",ROW($A$2:$A$32)),ROW($1:$31))))-1)),2,0),"无")接下来就简单了。VLOOKUP函数抓取，IFERROR函数将错误值转换为“无”，不再赘述了。
本期内容练习文件提取方式：
链接: https://pan.baidu.com/s/157LPx5m7zbmhsmnqE8udag?pwd=kqnv 提取码: kqnv
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
