Excel vlookup 反向查找的方法和公式的解释

excel 教程 - 八月 13, 2010 - 来源:单点日志 - 3 Comments -

这一篇主要讲解 vlookup的反向查询的用法,这样的反向查询方法也可以使用index和match配合使用达到同样的效果。
我们举个简单的例子:
1、单条件反向查找引用:有两个表Sheet1和Sheet2,Sheet1有100行数据,A列是学生学号,B列是姓名,Sheet2 表的A列是已知姓名,B列是学号,现在用该函数在Sheet1表中查找姓名,并返回对应的学号。
Sheet2表的B2的公式就可以这样输入:({}表示数组公式,要以CTRL+SHIFT+ENTER结束输入)
{ =VLOOKUP(A2,IF({1,0},Sheet1!$B$2:$B$100,Sheet1!$A$2:$A$100),2,FALSE) }
该公式通过IF函数改变了列顺序,利用常量数组{1,0}重新构建了一个新的二维内存数组,再提供给VLOOKUP作为查找范围使用。
    上述公式也可改用 =INDEX(Sheet1!$A$2:$A$100,MATCH(A2,Sheet1!$B$2:$B$100,0))
那么IF({1,0}这个怎么解释,IF({1,0}中的1用其它数代替也可以,如:IF({2,0}、IF({0.8,0}、IF({-1,0},但0只能用FALSE代替。因此,也可以这样理解:0等于FALSE,非0数值则等于TRUE。
在公式的中IF({1,0}只是公式中一部分if(a,b,c),if函数有三个参数,a为true执行b,a为false执行c,那么IF({1,0},Sheet1!$B$2:$B$100,Sheet1!$A$2:$A$100)这样的公式,简单的理解就是其中的参数a为{1,0},实际上{1,0}是一个水平数组他有两种情况一个是1一个是0,1表示true,0表示flase,因此两种情况都要执行,整个公式执行后就是把Sheet1!$B$2:$B$100和Sheet1!$A$2:$A$100两个区域合并了,而vlookup返回值为第2列的值即为A列,这样就达到了反向查询的效果。
总结实际上就是vlookup要查找的列必须在第一列,而我们就是利用数组公式的特性配合if公式,给vlookup组建一个满足vlookup要求的表范围,把条件列前面的内容移到后面去,而如果直接在vlookup中选取这个范围是反向的是不能直接选取的。
希望明白了。阅读全文,了解如何多条件查询
单点日志 excel教程 http://spoint.babyshoot.cn/


2、多条件查找引用:有两个表Sheet1和Sheet2,Sheet1有100行数据,A列是商品名称,B列是规格型号,C列是价格,Sheet2 表的A列是已知的商品名称,B列是已知的规格型号,现在用该函数在Sheet1表中查找商品名称、规格型号都相同的行所对应的价格填入Sheet2表的C列。 
   Sheet2表的C2的公式就可以这样输入:({}表示数组公式,要以CTRL+SHIFT+ENTER结束输入)
{ =VLOOKUP(A2&”|”&B2,IF({1,0},Sheet1!$A$2:$A$100&”|”&Sheet1!$B$2:$B$100,Sheet1!$C$2:$C$100),2,FALSE) }
用&将A2的名称和B2的规格合并成一个值来查找。这里增加”|”是为了避免因两个条件直接组合而出现本不相同的雷同,如名称”ABC”和型号”MN8″的组合,与名称”AB”和型号”CMN8″的组合相同。
   上述公式也可改用 { =INDEX(Sheet1!$C$2:$C$100,MATCH(A2&”|”&B2,Sheet1!$A$2:$A$100&”|”&Sheet1!$B$2:$B$100,0)) }
基本的原理:
一定能明白。
1、IF(条件,返回值1,返回值2)
首先你要弄懂这个函数,如果条件为真,函数的结果为”返回值1″,如果条件为假,函数的结果是”返回值2″:
if(true,10,100)=10
if(1,”A”,”B”)=”A”
if(false,20,30)=30
if(0,”你”,”我”)=”我”
2、{1,2,3,4}是一个数组,一个数组能存储多个数值,数组的表示方式是{}。
{1,0}是个水平数组,它有两个值,一个是1,另一个是0。
{“张三”,”李四”,”王五”}也是一个水平数组,它有三个值。
3、连接符&也是这个公式中的一个知识点。
“A” & “B” & “C”=”ABC”
“Aaa”&”Bcd”&”123″=”AaaBcd123″
字符串可以连接,数组也可以连接:
{1,2,3}&{“A”,”B”,”C”}={“1A”,”2B”,”3C”}
单元格区域也可以连接:
假如A1:A10的数据为1到10,B1:B10的数据为a到j。
A1:A10&B1:B10的结果就是
1a
2b
3c

10j



无觅相关文章插件,快速提升流量

《Excel vlookup 反向查找的方法和公式的解释》由单点日志原创提供!
转载请注明:http://spoint.babyshoot.cn/archives/2010/08/excel-vlookup.html

3 Comments│赶紧发话! »

发表评论

您必须登录后才能发表评论。
8