编按：

Hello小伙伴们，不知道大家有没有这样的体验，学习了很多Excel函数，但到了要用的时候却一个都想不到。今天我们就用一个“优秀员工组别查找”的工作案例，来实际运用一下那些常见的函数。INDEX、OFFSET、INDIRECT、LOOKUP和HLOOKUP等函数用法不尽相同，但对于这个工作难题，你随便挑其中一个就能轻松解决。你准备好见证奇迹了吗~

最近小李遇到这样一个问题，公司将总部人员分了四个组去支援门店的促销活动，分组信息如图所示：

在支援结束后，根据门店的反馈，选出了12名优秀员工。领导让小李把这些员工所在的组别填一下，完成后是这样的。

因为领导要的比较急，而且人数也不是很多，所以小李就手工一个一个查找出来先完成了工作。但是事后小李觉得这个问题应该有公式可以一次下拉就得出结果的，所以就来求助看看该用什么公式来完成这个工作。小李的这种探索精神是值得肯定的，很多同学日常都会遇到各种各样的工作任务，有的任务确实可以手工去处理，但如果不去思考更快捷的解决方法，不但丧失了一次学习锻炼的机会，而且在下次遇到同样的问题时就只能干瞪眼。

一.用COLUMN和MAX函数计算出优秀员工对应的列号

回到正题，要用公式解决这个问题，其实方法还蛮多的，但是核心思路就一个，要能确定每个优秀员工在分组表里的第几列。

为了便于理解，把优秀员工和分组名单放到一起。=(F2=A2:D12)，用第一个优秀员工的名字与分组名单的名字做对比，结果是一个区域数组，其中只有一个是TRUE。

在Excel365版本中，借助数组公式自动扩展的功能，可以直观的看到这个TRUE所在的位置。

非365的版本只能借助F9功能键来看了。

在上述比较运算后面乘区域的列号，公式为=(F2=A2:D12)*COLUMN($A$2:$D$12)，就可以得到TRUE所在位置的列号。

Excel365中的效果：

其他版本用F9的效果：

注意，此处的COLUMN函数用于获取列号，使用格式COLUMN(reference)，其中Reference为需要得到其列标的单元格或单元格区域。典型用法有三种。具体用法可以参考：会用Column吗？ 它让公式不那么笨。

接下来要做的就是：在这组数中用MAX把最大值提取出来，得到姓名在分组区域中的列号。

公式为：=MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12))，按“Ctrl+Shift+Enter”键结束。

二. 根据列号定位出组别

通过列号要得到对应的组别，INDEX、OFFSET、INDIRECT、LOOKUP和HLOOKUP函数都是可以办到。

INDEX解法

INDEX函数的语法为INDEX(array, row_num, [column_num])，用中文表达就是INDEX(数组或区域, 行号, 列号)。如果数组只包含一行或一列，则相对应的参数Row_num 或 Column_num 为可选参数，只需要写“行”号或者“列”号。所以INDEX解法的函数公式如下：

=INDEX($A$1:$D$1,MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)))，按“Ctrl+Shift+Enter”键结束。

OFFSET解法：=OFFSET($A$1,,MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12))-1,)，按“Ctrl+Shift+Enter”键结束。

INDIRECT解法：=INDIRECT("r1c"&MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),0)，按“Ctrl+Shift+Enter”键结束。

LOOKUP解法：=LOOKUP(MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),{1,2,3,4},$A$1:$D$1) ，按“Ctrl+Shift+Enter”键结束。

HLOOKUP解法：=HLOOKUP(MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),IF({1;0},{1,2,3,4},$A$1:$D$1),2,0)，按“Ctrl+Shift+Enter”键结束。

因为本例中的组别使用的是中文数字，所以TEXT函数也可以来凑个热闹。

TEXT解法：=TEXT(MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),"第[dbnum1]0组")，按“Ctrl+Shift+Enter”键结束。

以上这些函数的具体用法之前的教程都有过讲解，这里只是针对G列得到的列号来返回具体的内容。

至此，解决这个问题给出了6个方法，回顾一下解题思路，分成两个过程：首先计算出优秀员工对应的列号，然后根据列号定位出组别。

在计算列号的时候，用到了比较运算和最大值函数，其实这也是一个典型的条件最大值问题。

三. 计算优秀员工对应列号的其它方法

除了前文给出的方法之外，还有MAX+IF组合的套路和SUMPRODUCT函数的解法。

MAX+IF组合：=MAX(IF(F2=$A$2:$D$12,COLUMN($A$2:$D$12)))，按“Ctrl+Shift+Enter”键结束。

SUMPRODUCT方法：=SUMPRODUCT((F2=$A$2:$D$12)*COLUMN($A$2:$D$12))

这些都是很常用的公式套路，可见每个问题背后都有多种多样的解法，但前提是大家一定要多思考，同时多积累经验，只有在不断的实战过程中，运用公式和函数的能力才能得到提高。

最后给大家留一个思考题吧！如果本例中的分组明细不是这种格式，组别是位于A列的话，你会调整最终的公式吗？

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

７个Excel小技巧，提高表格查看效率

Excel运用规范1：一个单元格只记录一条信息

快速整理不规范的Excel表格的7个公式

9条最实用的计算excel中关于日期的公式！（建议收藏）

版权申明：

本文作者郅龙；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。