上一篇文章我们初步了解了VLOOKUP的简单操作和查找原理,以及使用VLOOKUP函数避免出错的注意事项,这一篇文章,我们来讲一下VLOOKUP第一参数如何规范查找值。
一.查找值首尾空格,非打印字符
查找值首尾有空格的情况下,直接用VLOOKUP函数匹配结果一定是#N/A错误,这个时候,我们用一个TRIM函数来删除掉查找值中多余的空格
=TRIM(text),文本是姓名“张小”所在的F4单元格,即=TRIM(F4),将这个TRIM函数作为VLOOKUP函数的第一参数
去除首尾空格完整公式为:=VLOOKUP(TRIM(F4),B2:D12,3,0),这是对于查找值首尾空格的处理方法。
若查找值有非打印字符,我们把TRIM函数替换成CLEAN函数即可
清除非打印字符完整公式=VLOOKUP(CLEAN(F4),B2:D12,3,0)
二.查找值首尾中间都有空格
在查找值首尾和中间都有空格的情况下,TRIM函数做不到把空格都去掉,这时,我们就要用一个文本替换函数---SUBSTITUTE函数
这个函数是将文本字符串中的一部分字符串用新的字符串来替换
=substitute(text,old_text,new_text,[instance_num])
text文本:就是F4单元格,
old_text旧文本:我们要把空格处理掉,所以旧文本就是空格,输入一对双引号,在引号中间输入一个空格=“ ”
new_text新文本:新文本我们直接输入一对双引号=“”
完整公式:=SUBSTITUTE(F4," ",""),作为VLOOKUP函数的第一参数
=VLOOKUP(SUBSTITUTE(F4," ",""),$B$2:$D$12,3,0),输入完公式向下填充
三.若查找区域的首列包含空格
若是查找区域首列包含空格,我们同样使用SUBSTITUTE函数来去除空格
公式为=SUBSTITUTE($B$2:$B$11," ",""),这里第一参数B2:B11使用了数组
去除掉多余空格后,我们需要用到一个IF({1,0},区域,区域)这样的结构来构建一个查找区域
我们先来看一下完整IF公式=IF({1,0},SUBSTITUTE($B$1:$B$11," ",""),$D$1:$D$11)
IF函数的参数结构:=IF(条件表达式,表达式成立,表达式不成立)
条件表达式的结果是逻辑值,不是真(true),就是假(false)
在IF中0就代表false,任何不为0的值都为真,也就是true
{1,0}是一个返回一行两列的内存数组,嵌套在IF中,表示当逻辑值分别为1和0时返回什么值
当=IF(1,返回用SUBSTITUTE函数去除空格的数据,即SUBSTITUTE($B$2:$B$11," ","")
当IF第一参数为0时,即表达式不成立,则返回D1:D11这个区域
完整VLOOKUP公式:=VLOOKUP(F4,IF({1,0},SUBSTITUTE($B$1:$B$11," ",""),$D$1:$D$11),2,0)
注意:因为是数组公式,输入完公式后按CTRL+SHIFT+回车三键返回值,并向下填充公式