Excel多对多查询,三种应用场景,有以下五个通用公式情况,耗时一小时总结。
一、查询值在首列,返回所有列,可以使用VLOOKUP+COLUMN。
多对多第一种情况=VLOOKUP($G2,$B$2:$E$11,COLUMN(B:B),FALSE)
这个公式输入在第一个单元格,然后向右填充,在选中一行三列,向下填充公式。
二、查询值在首列,但是返回列是错乱的。VLOOKUP+MATCH。
多对多第二种情况=VLOOKUP($G8,$B$2:$E$11,MATCH(H$7,$B$1:$E$1,0),FALSE)
其中match函数主要用于返回列号,这个函数的用法我以前也讲过。
三、查询值不在首列,返回指定列。index+match+match。
多对多查询第三种=INDEX($B$2:$F$11,MATCH($H2,$C$2:$C$11,0),MATCH(I$1,$B$1:$F$1,0))
这种情况下,使用VLOOKUP已经不行了,因为工号在查询值的左侧,也就是说是逆向查询,如果使用VLOOKUP就太复杂了,所以使用了index+match+match。
四,使用FILTER嵌套,返会指定列数据。
=FILTER(FILTER($B$1:$F$11,$B$1:$F$1=I$1),$C$1:$C$11=$H2)
filter函数可以一对多,也可以多对多,简单的情况很好处理,这种事处理无序数据。
五、使用xlookup嵌套,返回指定列数据。
=XLOOKUP($H2,$C$2:$C$11,XLOOKUP(I$1,$B$1:$F$1,$B$2:$F$11))
同样xlookup嵌套也可以返回数据,大家注意下单元格的引用方式,使用复杂函数的时候,引用方式一定要对。