送人玫瑰，手有余香，请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径！
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好，今天要和大家分享一则和数据查找、提取有关的题目。
原题目是这样的：
左侧是源数据，右侧表格中已知了成绩，怎样将对应的代码提取出来。由于有重复值，因此不能直接使用VLOOKUP函数了。有什么好的方法和思路呢？
万金油公式
在单元格E2中输入“=INDEX(A:A,SMALL(IF(F2=$B$2:$B$19,ROW($2:$19)),COUNTIF($F$2:F2,F2)))”，三键回车并向下拖曳即可。
思路：
这是一个万金油公式的套路，其它部分都很简单，不再过多介绍了。
特别讲一下COUNTIF($F$2:F2,F2)部分。在动态区间$F$2:F2，统计F2中的个数。如果有重复值，那么随着公式向下拖曳，它返回的值会逐次增加。作为SMALL函数的第二个参数，它的变化将会改变INDEX函数最最终结果的返回，从而实现了提取不同的代码。
在单元格E2中输入“=OFFSET(A$1,MATCH(LARGE(B:B,ROW(A1)),IF(COUNTIF(E$1:E1,A$2:A$19)=0,B$2:B$19),),)”，三键回车并向下拖曳即可。
思路：
LARGE(B:B,ROW(A1))部分，找出第1、2、3..大的值
IF(COUNTIF(E$1:E1,A$2:A$19)=0,B$2:B$19)部分，返回满足条件的清单。这里E$1:E1是一个常用的技巧
MATCH函数返回第几大值在清单中的位置
OFFSET函数返回正确答案
这里特别讲一点COUNTIF(E$1:E1,A$2:A$19)=0的作用。随着区域的扩大，在E$1:E1中的数值，COUNTIF函数都会返回值1，也就是不满足“=0”的条件，就会被排除在IF(COUNTIF(E$1:E1,A$2:A$19)=0,B$2:B$19)函数返回的清单之外，从而实现了查找不同代码的目的。
在单元格E2中输入公式“=INDEX(A:A,MOD(SMALL(ROW($2:$19)-$B$2:$B$19/1%%,ROW(A1)),100))”，三键回车并向下拖曳即可。
一句话解释：
这个公式实际上利用了加权的处理方法。ROW($2:$19)-$B$2:$B$19/1%%部分，在成绩相同的情况下，扩大1万倍后，越小的行号与其差越小，会被首先提取到。
请看一下下面这个公式提取的效果和上面的这个公式的有什么不同。
它的公式是“=INDEX($A$2:$A$19,MOD(LARGE($B$2:$B$19*10000+ROW($1:$18),ROW(A1)),100))”
加权上不同的操作，带来的效果是各不相同的。
在单元格E2中输入公式“=INDEX(A:A,MOD(-LARGE($B$2:$B$19*10^6-ROW($2:$19),ROW($A1)),10^3))”，三键回车并向下拖曳即可。
一句话解释：
这个公式也用到了加权的技巧。这次由于是对成绩进行加权，再减去行号。由于这里是减去行号，直接用MOD函数求余就得不到行号了，因此在MOD函数求余是用了个小技巧。
MOD函数两个参数异号整数求余：
1、函数值符号规律(余数的符号) mod(负，正)=正 mod(正，负)=负。
结论：两个整数求余时，其值的符号为除数的符号。
2、取值规律，先将两个整数看作是正数，再作除法运算。
①能整除时，其值为0 （或没有显示）。
②不能整除时，其值=除数×(整商+1)-被除数。
-END-
联系客服
