VLOOKUP 是大家最常用的查询函数,咱先说一下VLOOKUP 的语法。
语法:

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

语法解析:

lookup_value:找什么(查找值)
table_array:哪里找(查找区域)
col_index_num:第几列(返回值在查找区域的第几列)
range_lookup:精确还是模糊(0 或 1,默认为 1)

在使用 VLOOKUP 的时候,对于第 2 个参数(即查找值与返回值所在的单元格区域),查找值所在列必须在返回值所在列之前(即从左往右查找顺序),不然函数无法工作。
如果需要查询键盘对应的存货编码,则需要将 A、B 两列进行对换,即存货名称这一列必须在存货编码这一列的前面!那么,图中的情况下,该怎么查询呢?每次都要将两列数据进行互换吗?如下图所示:
14069763-800c9a50a16e4472.webp.jpg
这种逆向查询(从右往左)方法,有6种办法。


1.LOOKUP
=LOOKUP(1,0/(E3=B2:B10),A2:A10)
14069763-65b4d078be41568e.webp.jpg


2.INDEX + MATCH
=INDEX(A2:A10,MATCH(E3,B2:B10,0))
14069763-9012ca5b6a671550.webp.jpg
简单说明,MATCH 函数也是查找函数,但是它返回的不是单元格的值,而是行号或者列号。比如MATCH(“显卡”,B2:B10,0) ,返回的是 7 这个数值,也就是说 显卡B2:B10 的第 7 行这个位置。而 INDEX 函数的作用是,返回 A2:A10 这个区域第几行的内容,例如 INDEX(A2:A10,7) ,就会返回 A2:A10 这个区域的第 7 行内容,也就是 B0007 这个值。


3.INDIRECT + MATCH
=INDIRECT("A"&MATCH(E3,B2:B10,0)+1)
14069763-563597e2e23ef0b9.webp.jpg
INDIRECT 函数的作用是将文本转换为真正可以使用的公式。比如在单元格里输入 "=A8",回车之后你只能看到 =A8 这几个字符,而不是 A8 这个单元格的内容 B0007

但是INDIRECT 函数就可以做到。

咱们又换一个查询内容,这次就用 手机 。通过 MATCH 函数返回手机在 B2:B10 的第 2 个位置,也就是 B3 单元格,行号是 3,所以这里需要 +1。对应的存货编码也是在第 3 行,所以咱们只要构造出 A3 就可以得到手机对应的存货编码了。这个时候,把这些字符往 INDIRECT 函数里面丢进去就行啦,最后类似于 INDERECT("A"&2+1) 这样的存在。回车之后是不是返回正确的值。


4.OFFSET + MATCH
=OFFSET(A1,MATCH(E3,B2:B10,0),)
14069763-858ee5eabec9e8d6.webp.jpg
OFFSET 函数是将基点单元格进行移动的函数,可以返回移动后单元格的值。它有 3 个必填参数:

基点单元格
移动的行数:向下移动为正数,向上移动为负数
移动的列数:向右移动为正数,向左移动为负数
注意到上面公式里最后一个 吗,因为这里用不到第 3 参数,但是它又是必填的,所以这里只能填写一个逗号作为代替。

拿查找 鼠标 为例,它在 B2:B10 的第 4 个位置,利用 MATCH 函数即可返回这个值。然后利用 OFFSET 的位移功能,将 A1 向下移动 4 个单元格即 A5 ,是不是返回鼠标对应的存货编码.


5.VLOOKUP + IF
=VLOOKUP(E3,IF({1,0},B2:B10,A2:A10),2,0)
14069763-a3c8388cfe13da7e.webp.jpg
另外 3 个参数不难理解,核心在于用 IF 函数和数组公式构造的第 2 参数。简单的说,就是利用 IF 函数,值为 1 即 TRUE 的时候返回 B 列单元格,值为 0 即 FALSE 的时候返回 A 列单元格, 重新构造查找区域,也就是 B 列存货名称在前、A 列存货编码在后的新单元格区域。这个时候, VLOOKUP 函数就可以正常查找了。


6. VLOOKUP + CHOOSE
=VLOOKUP(E3,CHOOSE({1,2},B2:B10,A2:A10),2,0)
14069763-6571dde85c21de43.webp.jpg
这里就是将 IF 函数替换为 CHOOSE 函数,效果是一样的。注意哦,这里不是 {1,0} 而是 {1,2}

Last modification:October 23, 2019
If you think my article is useful to you, please feel free to appreciate