vlookup怎么反向查找(反向vlookup函数的使用方法)
Vlookup函数是基本的数据查询函数,有时单条件的正向查找并不能满足我们的需求。这篇文章为朋友们分享Vlookup函数的三种偏门用法。
一.Vlookup函数实现动态求和:
1.动态效果图:
2.操作方法:
(1)在J2单元格通过数据验证制作一个简单的下拉列表,方便选择不同的姓名。
(2)在K2单元格输入公式=SUM(VLOOKUP(J2,A:H,COLUMN(C1:H1),0)),输入公式要以Ctrl Shift Enter三键确定。
3.公式解析:
(1)COLUMN(C1:H1)返回的是一个由C1:H1单元格所在的列{3,4,5,6,7,8}构成的数组。
(2)使用Vlookup函数可以分别查找到J2单元格姓名所对应的语文、数学、英语、物理、化学、生物的成绩。
(3)最后用SUM函数对J2单元格姓名所有的成绩求和,当J2单元格内容变化时就实现了动态求和。
二.Vlookup函数实现反向查找:
1.操作方法:
(1)在J2单元格通过数据验证制作一个简单的下拉列表,方便选择不同的姓名。
(2)在K2单元格输入公式==VLOOKUP(J2,IF({1,0},B1:B20,A1:A20),2,0)
2.公式解析:
(1){1,0}是一个由数字1和0构成的数组,这个数组作为if函数的判断依据;当判断依据为1时,返回B列单元格的内容;当判断依据为0时,返回A列单元格的内容;IF({1,0},B1:B20,A1:A20)函数构造了一个A列和B列对应单元格内容互换的数组。(动态图中有操作演示)
(2)vlookup函数就可以利用构造的位置互换的数组就可以正常查找。
三.Vlookup函数实现多条件查找:
1.说明:
当数据存在重复值时,简单的单条件查找并不能准确的查找出结果。(例如在这个实例中三年一班和三年二班都有姓名为张志远的同学)
2.操作方法:
在L2单元格输入公式=VLOOKUP(J2&K2,IF({1,0},A1:A19&B1:B19,C1:C19),2,0)。
3.公式解析:
(1).J2&K2是连接J2单元格与K2单元格的内容,在图中所示连接后的内容为“三年一班张志远”。
(2)IF({1,0},A1:A19&B1:B19与上述反向查找类似。只是当if函数的判断依据中返回的是A列对应单元格内容和B列对应单元格内容的连接,最终返回的是一个A列对应单元格内容和B列对应单元格内容的连接以及C列单元格内容构成的虚拟数组(动态图中有操作演示)。
(3)vlookup函数就可以利用构造的AB列连接以及C列构造的数组就可以正常查找。