行列交叉查询公式汇总及解释 / 开普饭

一、需求

根据指定的职员和月份，用公式查询相应的金额。比如要编制查询公式查询出5月职员A04的金额345，如下图：

查找某项值在数据系列中是第几个，我们可以用MATCH函数，该函数的作用就是“计算排行老几”。其语法格式为：

＝MATCH（查找值，查找区域，查找模式）

查找模式为0时为精确查找，将返回第一个完全等于“查找值”的位置数。比如下图中公式：

计算“李四”的位置数，将返回2，而不会是4。

1、使用VLOOKUP函数

我们知道VLOOKUP是用于查找指定值，找到后，返回查找区域中查找值所在行指定列的值。其语法格式为：

＝VLOOKUP（查找值，查找区域，返回查找区域的第几列，查找模式）

第四参数为0时表示精确查找，为1时表示模糊查找。

因而，我们要查找“职员A04”在“5月”的金额，公式为：

=VLOOKUP(A10,$A$2:$I$8,6,0)

但是，由于公式中的第三参数6是固定的，不能随指定的月份变化而变化，需要手工修改。为了让公式第三参数返回的列要随月份变动而变动，我们应将6改用函数来计算得出。

方法1：使用MATCH函数计算5月在A1:I81单元格区域中是第几个，

公式为：

＝MATCH(B10,$A$1:$I$1,0)

当然，也可以写成

＝MATCH(B10,$B$1:$I$1,0)+1

方法2：直接将“5月”中的数字“5”提取出来，

我们用函数SUBSTITUTE将“月”字删除。公式：

＝SUBSTITUTE(B10,"月","",1)

公式计算结果是5，而要取第6列的值，故要加1。然后将此函数套入到VLOOKUP中，完整的公式为：

=VLOOKUP(A10,$A$2:$I$8,SUBSTITUTE(B10,"月","",1)+1)

我们也可以直接取月份的左边1位或2位，那到底是取1位还是2位呢？当月份字符个数为2时（1月－9月）取1，当月份字符个数为3时（10月－12月）取2，还是公式来计算，取月份字符个数[len()]减掉1，用公式表示就是：

＝LEN(B10)-1

取左边的数字：

＝LEFT(B10,LEN(B10)-1)

将其套入到VLOOKUP函数中，完整的公式为：

=VLOOKUP(A10,$A$2:$I$8,LEFT(B10,LEN(B10)-1)+1)

2、使用OFFSET函数

我们理解了上面VLOOKUP的公式后，使用OFFSET公式就比较简单了。

OFFSET函数的作用就是往下数几行往右数几列，然后取几行几列。其语法格式

＝OFFSET（基准单元格，往下数几行，往右数几列，取几行，取几列）

函数的第二三四五参数可以为负，为负数时表示反方向。比如第二参数为－2时，表示往上数二行。第五参数为－3时，表示往左取3列。因而，查找“职员A04”在“5月”的金额其公式为：

=OFFSET(A1,4,5,1,1)

上面的可以简写为：

=OFFSET(A1,4,5)

为了让公式灵活，我们可以用MATCH函数来计算公式中的4和5，套用前面VLOOKUP中的MATCH公式，其完整的公式为：

=OFFSET(A1,MATCH(A10,A2:A8,0),MATCH(B10,$B$1:$I$1,0))