输入表与负责部分的项目顺序不同时

在刚才的例子中,为了让“输入表”与“负责部分”的项目排列顺序保持一致,第三参数按顺序输入2、3、4……这样连续的序号。因此,VLOOKUP 函数第三参数引用嵌入了 COLUMN 函数,这样做会提高效率。

但是,如果像下面这样,输入表与负责部分的项目顺序不同时该怎么办?也就是说第三参数不是连续数字的话,各单元格中的 VLOOKUP 函数即便运用了 COLUMN 函数,也无法得出正确的第三参数。

输入表与负责部分的项目顺序不同时

如何用 VLOOKUP函数应对检索范围中竖列顺序的变动状况-Excel22

此例中,D 列的“单价”对应“负责部分”最左端往右数第6列,E 列的“生产者”对应“负责部分”最左端往右数第5列。在这样的前提下,如果想要在单元格 C3中输入最开始的那个函数公式,之后只要复制到 G 列也都可以得出结果的话,我们应该怎么做呢?

在 C 列商品名的单元格输入的 VLOOKUP 函数中第三参数应该是什么数字呢?答案是2。那么,我们只要输入能自动导出数字2的第三参数就可以了。这时候,我们就要用到 MATCH 函数。

我们通过以下例子具体解释一下。

如何用 VLOOKUP函数应对检索范围中竖列顺序的变动状况-Excel22

上述例子中,A1到 D1项目名称分别为“商品名”、“单价”、“生产者”、“最低订购单位”,这些项目在 F1到 I1的范围中位于左数第几列,会相应地显示在 A2到 D2中。以单元格 A2为例,“A1(即商品名)的值,在 F1:I1范围里位于左数第2个”,那么 A2中则会显示数字2。

在单元格 A2做出这种处理的是下面的函数公式。

=MATCH(A1,$F$1:$I$1,0)

MATCH 函数中第一参数指定的值,会导出在第二参数指定范围中位于第几位的数字。第三参数基本上“只要输入0就行了”。

在图中,将单元格 A2的公式一直复制粘贴到 D2。因为第一参数不做绝对引用,单元格 B2里被复制粘贴的公式中的第一参数为 B1,单元格 C2里被复制粘贴的公式的第一参数为 C1,D2中则是 D1。

第二参数限定了纵列或横行的范围。

▲指定纵列的范围

第一参数指定的值为在此范围内的上数第几行。

▲指定横行的范围

第一参数指定的值为在此范围内左数第几列。

单元格范围限定为 F1:I1,则呈现如下状态:

  • 单元格 A1即“商品名”位于左数第2个
  • 单元格 B1即“单价”位于左数第4个
  • 单元格 C1即“生产者”位于左数第3个
  • 单元格 D1即“最低订购单位”位于左数第1个

能够在单元格中显示数字2、4、3、1,是因为 MATCH 函数的处理。

在 VLOOKUP 函数的第三参数中加入 MATCH 函数,即使“输入表”与“负责部分”的项目的排列顺序不同,也能够通过 MATCH 函数取得“‘输入表’的各项目名在‘负责部分’下位于第几列”的数字,把这样的结构嵌入 VLOOKUP 函数第三参数中就能够解决顺序不同的问题。在输入表的单元格 C3,请输入以下公式:

=VLOOKUP($B3,$I:$N,MATCH(C$2,$I$2:$N$2,0),0)

然后复制到整个表格,画面则显示如下:

在单元格 C3中输入=VLOOKUP($B3,$I:$N,MATCH(C$2,$I$2:$N$2,0),0)并复制粘贴至全表

如何用 VLOOKUP函数应对检索范围中竖列顺序的变动状况-Excel22

分析 MATCH 函数的处理

可能乍一看上述的公式很复杂,接下来我们来仔细分析一下。关键在于理解嵌入 VLOOKUP 函数第三参数的 MATCH 函数是如何发挥作用的。

MATCH(C$2,$I$2:$N$2,0)

这个公式得出的数字指向的是,第一参数指定的单元格 C2的值(即商品名的值)位于第二参数指定范围($I$2:$N$2)的左数第几个。在这一例子中为数字2,它与单元格 C3中以 B3的值(数字1)为检索值的 VLOOKUP 函数里,检索范围 I:N 从左数第几列的对应数字是一致的。

将输有单元格 C3内容的单元格一致复制粘贴到 G6,为了不让参照项移位,需要设定绝对引用。