|
一.最常见的INDEX+MATCH组合 首先了解INDEX语法结构: INDEX(reference,row_num,column_num,area_num) 即:INDEX(一个或多个单元格区域的引用,返回引用行序号,返回引用列序号,交叉点的引用区域) 注:这个函数的第四参数很少用到 第一参数即要返回的区域引用B4:D11 那么怎样返回引用区域的行序号? 可以采用MATCH定位,比如这里MATCH($B14,$C$4:$C$11,) "乙"所处位置在姓名开始计数第3行 而要求得的成绩在以班级开始计数的第3行,也用MATCH定位 所以,组合各函数而获得公式: INDEX($B$4:$D$11,MATCH($B14,$C$4:$C$11,),MATCH(C$13,$B$4:$D$4,)) 注:加锁定行列标$是为了拉动时区域不会变动.要注意两个MATCH锁定行列标的区别. 二.OFFSET+MATCH组合 获取查找值的行号和列号方法同一,用MATCH. 而使用的OFFSET函数是以指定的引用为参照系，通过给定偏移量得到新的引用 然后先了解OFFSET语法结构 OFFSET(reference,rows,cols,height,width) 即:OFFSET(偏移量参照系的引用区域,上下偏移行数,左右偏移列数,高度,宽度) 首先赋予偏移量的基准点,示例位置是A18 从而组合得到完整公式: OFFSET($A$18,MATCH($B29,$C$4:$C$11,),MATCH(C$13,$B$4:$D$4,)) 这里只返回交叉点,所以省略高度和宽度参数. 三.VLOOKUP+CHOOSE\IF VLOOKUP正常的查找只能是从左至右,即首列为包括匹配值的列,然后向右开始查找 而要实现从右至左这种反向查找,就要使用CHOOSE或IF将区域换位 如果需要一个公式拖动的话,这个公式还需要加其他函数. 所以这里成绩列是一个公式,班级列是另一个公式 C42=VLOOKUP($B42,$C$33:$D$39,2,) D42=VLOOKUP($B42,CHOOSE({1,2},$C$32:$C$39,$B$32:$B$39),2,) 或者:D42=VLOOKUP($B42,IF({1,0},$C$32:$C$39,$B$32:$B$39),2,) 四.LOOKUP 完整公式: C56=LOOKUP(1,0/($C$47:$C$53=$B$56),$D$47:$D$53) D56=LOOKUP(1,0/($C$47:$C$53=$B$56),$B$47:$B$53) 选取公式中的0/($C$47:$C$53=$B$56) 得到{#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!} 用LOOKUP会找到小于等于1的所在位置,从而返回对应区域所在位置的值 同样,如果需要一个公式拖动解决示例的话,也需要添加其他函数 如:C56=LOOKUP(1,0/($C$47:$C$53=$B$56),OFFSET($A$47,,MATCH(C$55,$B$46:$D$46,),7)) 五.INDIRECT+TEXT+MATCH 利用INDIRECT对R1C1样式的引用功能 还是先用MATCH对查找值进行定位,第一个MATCH返回所在行,因为示例起始行在60, 所以加上60,如果整列查找,不用加,得到一个结果后,为了和TEXT返回一个R1C1样式, 根据需要,行号需扩大10倍,和第二个MATCH定位的位置相加,得到一个数字. 示例为利用TEXT返回"r65c4",然后套入INDIRECT,得到结果. 完整公式: INDIRECT(TEXT((MATCH($B$70,$C$61:$C$67,)+60)*10+MATCH(C$69,$B$60:$D$60,)+1,"r0c0"),) 自营广告可以了！有需求的可以联系小编。 以上方法中，从左至右可以选择比较简单的VLOOKUP，这里主要是为了一个公式拖动而解决。 当然,还有其他函数套路也可以达到反向查找的目的.比如返回的结果为数值,还可以利用SUM或SUMIF多条件求和.等等.根据需要用最灵活的方法解决实际问题才是目的. |
|
|
