前言 有的同学刚刚学会VLOOKUP函数,就已经感觉自己的EXCEL水平不错了,不过也的确如此,VLOOKUP函数看起来并不复杂,但真的用起来,你就会发现并非你想象的那么简单,因为VLOOKUP很挑剔,例如要查找的数据类型必须一致,必须在区域的第一列等等,返回值必须在查找值得右边。 如果要返回的值在查找值得左边,就属于反向查找,用常规的VLOOKUP函数已经不行了,需要用到加强版VLOOKUP(要查找的值,if({1,0},查找值列,返回值列),2)或使用INDEX+MATCH函数 正文 以上用法,大家可以参考以前的文章,今天继续加大一下难度:合并单元格的反向查找 以下图为例,第一列为项目的建造基地,且为合并单元格,第二列为项目编号。我们需要根据项目编号查找建造的基地。 方法一、采用LOOKUP,OFFSET,MATCH组合 公式如下: =LOOKUP('座',OFFSET($A$1,0,0,MATCH(D2,$B$1:$B$9,0),1)) 解释: 1、首先使用MATCH函数查找项目在B列的位置 MATCH(D2,$B$1:$B$9,0),返回值为4,即H486项目在$B$1:$B$9区域的第4个位置。 2、然后使用OFFSET函数返回一个新的区域,区域的大小根据项目所在的行来确定 OFFSET($A$1,0,0,MATCH(D2,$B$1:$B$9,0),1) =OFFSET($A$1,0,0,4,1) =A1:A4,意思是将A1向下偏移0个单元格,向右偏移0个单元格,新的区域行数为4,列数为1,实际得到的区域为A1:A4 3、最后使用LOOKUP函数查找该区域中出现的最后一个文本。 LOOKUP('座',OFFSET($A$1,0,0,MATCH(D2,$B$1:$B$9,0),1)) = LOOKUP('座',A1:A4) =”烟台” LOOKUP('座',查找区域),可以返回此区域的最后一个文本 LOOKUP(9E307,查找区域),可以返回此区域的最后一个数字 方法二、使用LOOKUP,INDIRECT,MATCH组合 =LOOKUP('座',INDIRECT('A1:A'&MATCH(D2,$B$1:$B$9,0))) 解释 =LOOKUP('座',INDIRECT('A1:A'&MATCH(D2,$B$1:$B$9,0))) = LOOKUP('座',INDIRECT('A1:A'&4) = LOOKUP('座',INDIRECT('A1:A4') = LOOKUP('座', A1:A4) =”烟台” 总结 不管是OFFSET还是INDIRECT函数,最终目的都是重新构建一个查找区域,这个查找区域的行数由查找值确定,最后返回这个区域的最后一个文本。 |
|
来自: hercules028 > 《excel》