INDEX+MATCH组合妙，数据查找少不了，一般人还不告诉他！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

今天要讲的这个例子是我们在工作中经常会碰到的一种情况。如下图。

要求查找的盘号是一个文本字符串，表示了一个范围区间。要解决这个问题，首先我们需要将盘号区间分解为独立的盘号，之后才可以进行查找操作。

01

观察一下源数据的特点。在源数据中，盘号是按照升序排列的，这样，我们就可以使用MATCH函数的近似查找了。

在单元格E2中输入公式“=IFERROR(INDEX($A$2:$A$18,IF(--RIGHT(D2,5)>MAX(--RIGHT($B$2:$B$18,5)),"",MATCH(D2,$B$2:$B$18))),"无批次号")”，三键回车并向下拖曳即可。

思路：

  • IF函数部分，是把要查找的盘号和盘号范围的上限做一个比较，超过上限的就不存在批次号信息，在范围内的，就可利用MATCH函数的近似查找功能了

  • MATCH(D2,$B$2:$B$18)部分，省略了第三个参数1，表示要查找比目标值小的最大值

  • IF函数返回的值作为INDEX函数的第二个参数，INDEX函数返回正确结果

  • IFERROR函数做容错处理

如果源数据不是按照升序排列的，这里的公式书写起来还要复杂些。朋友们如果有兴趣可以自己动手试一试。

02

其实这类问题很适合使用FREQUENCY函数。这里为了相对简单地向大家介绍如何使用FREQUENCY函数，我们就不考虑盘号超过上限的问题了。

在单元格E2中输入公式“=INDEX(A:A,MATCH(1,FREQUENCY(--MID(D2,6,5),--MID($B$2:$B$18,{6,17},5)),)/2+1)”，三键回车并向下拖曳即可。

思路：

  • --MID(D2,6,5)和--MID($B$2:$B$18,{6,17},5)部分，分别提取盘号前后范围的数字部分，并分别做为FRQUENCY函数的第一和第二参数

  • 利用FREQUENCY函数进行计频。由于目标盘号是唯一的，所以FREQUENCY函数返回的结果只有可能是1

  • 利用MATCH函数来查找上面的“1”在内存数组中的位置

  • 最后利用INDEX函数返回正确结果

好了朋友们，今天和大家分享的内容就是这些了！喜欢我的文章请分享、转发、点赞和收藏吧！如有任何问题可以随时私信我哦！

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

我就知道你“在看”

推荐阅读
赞 (0)

相关推荐