合并单元格VLOOKUP对应值,你会吗?

今天,VIP群内有3个问题,都是合并单元格下VLOOKUP对应值,一起跟着卢子来看看。

1.上面是好多个月每个项目的数据,每个月有4列内容。现在选择某个项目,就将相应的数据引用过来。

相当于3个条件查找,项目、月份、类型。假如现在查找1月,类型是按顺序返回2、3、4、5列,就用COLUMN(B1)右拉获取。

=VLOOKUP($B$13,$B$5:$F$8,COLUMN(B1),0)

假如现在查找2月,类型是按顺序返回6、7、8、9列,就用COLUMN(F2)右拉获取。

不过这样修改挺麻烦的,得想通用的办法。

往下拉1行,就相当于向右4列,这里可以用4*ROW(A1)。

=VLOOKUP($B$13,$B$5:$V$8,4*ROW(A1)-4+COLUMN(B1),0)

这里也可以用MATCH判断月份在哪列,再加+COLUMN,后面的案例会说。

2.还是刚刚的表格,现在是以类型作为下拉选择,同样是3个条件查找。

类别因为是下拉选择,就不是按顺序,这里就需要用MATCH判断在哪列。总成本支出,在所选区域第1列。

=MATCH($B$22,$C$4:$F$4,0)

月份除了可以用4*ROW(A1)这种判断,也可以用MATCH判断。

=MATCH($B23,$C$3:$V$3,0)

返回第几列就有下面2种方式。

=MATCH($B23,$C$3:$V$3,0)+MATCH($B$22,$C$4:$F$4,0)

=4*ROW(A1)-3+MATCH($B$22,$C$4:$F$4,0)

现在就可以获得最终公式。

=VLOOKUP(C$22,$B$5:$V$8,4*ROW(A1)-3+MATCH($B$22,$C$4:$F$4,0),0)

3.根据姓名、分类查找相应的得分。

跟前面案例有所不同,现在是返回的列确定,返回的行不确定。

用前面的方法,用MATCH可以判断姓名在第几行。

=MATCH($S5,$B:$B,0)

分类在第几行。

=MATCH(T$4,$C$5:$C$9,0)

姓名的行数+分类的行数-1,就得到这2个条件对应的行。比如张三,其他小组成员是在第9行,而实际公式得到5+5=10,多了1行,这就是后面-1的意思。

=MATCH($S5,$B:$B,0)+MATCH(T$4,$C$5:$C$9,0)-1

知道行数,再嵌套INDEX就得到相应的对应值。

=INDEX($Q:$Q,MATCH($S5,$B:$B,0)+MATCH(T$4,$C$5:$C$9,0)-1)

查找内容不一定要用某个方法,而是要善于发现数据源的规律,用最合适的方法。

推荐:INDEX函数最经典的4种用法(推荐收藏)

上文:11个求和、计数案例,争取都学一次!这个要收藏!

看到合并单元格,你的第一想法是什么?

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

(0)

相关推荐