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

菜鸟锐

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

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

hytsky

很好很强大,you are very strong you are very good {:361:}
不知道讲对了没有````{:101:}

菜鸟锐

hytsky 发表于 2011-12-7 09:02
很好很强大,you are very strong you are very good
不知道讲对了没有````

very good! very strong!

hytsky

菜鸟锐 发表于 2011-12-7 09:05
very good! very strong!

{:201:}{:111:}{:031:}

yiguo

想得很详细，听懂不得也没关系，照着文章写公式就好了，不过还是收藏先。{:3312:}

201228

very good! very strong!   
or： very good and very strong。

远远65

我还是看不明白 操作不行啊！

有没有动画教程？ 十分感谢，也特别需要这个反向查找函数！

chen_pinghui

楼主总结的真好，我一直只会用，不会讲
if({1,0} 换成 choose({1,2} 也完全相同。