excel 是否能利用VLOOKUP函数获得检索列左侧的数值？

Excel函数 2023-01-08

VLOOKUP 函数下，无法取得检索列左侧的数值

VLOOKUP 函数可以说是 Excel 中最重要的函数，这里让我们再来看看其具体的公式和功能。

【公式】

=VLOOKUP(检索值,检索范围,列数,0)

【功能】

在检索范围最左一列中查找与检索值相同的单元格，然后在该单元格中返回第三参数指定的列数中的某个单元格的值。

“从检索范围的最左边的列返回到第三参数指定的列数中的某个单元格的值”，也就是“返回位于该列右侧的值”。

那么，问题就来了。

“难道无法直接用这一列左侧的数值吗？”

“给第三参数做减法导出数值就可以了吧？”或许有许多人都抱有这样的疑问。但答案是：“不可以”。

那么，如果想要获得位于检索列左侧的列中的数值，应该怎么办？

什么是 OFFSET 函数

组合使用 OFFSET 函数与 MATCH 函数可以解决前文中的问题。OFFSET 函数的本质是“确定作为基准的单元格，通过上下左右偏移得到新的区域的引用”。

【公式】

=OFFSET(基准单元格,偏移行数,偏移列数)

【功能】

是以基准单元格为起始，返回按移动行数、移动列数偏移的单元格的值。

偏移行数，正数表示向下，负数表示向上。

偏移列数，正数表示向右，负数表示向左。

首先，举个非常简单的例子。

➊ 在 Excel 工作表的单元格 C3中输入“100”。

➋ 将下列公式输入任意一个单元格。

=OFFSET(A1,2,2)

输入有上述公式的单元格，将返回“100”。

作为基准单元格的 A1，向下2行、向右2列的目标单元格是 C3（值为100）。所以输有此公式的单元格所返回的值就是100。

将 OFFSET 函数与 MATCH 函数组合

运用这个公式，想办法引用检索列左侧的单元格。

下列表格我们可以看到，按照单元格 E2的数字，在 F2、G2的“课程”和“单价”中会分别对应返回数据。首先，先在 E2里输入1。

首先，F2的“课程”十分简单，通常使用 VLOOKUP 函数就能处理。

=VLOOKUP(E2,B:C,2,0)

在单元格 F2中输入=VLOOKUP(E2,B:C,2,0)后取得“课程”数据

但是，单元格 G2的“单价”数据位于单价的检索列（B 列）的左侧，这样用 VLOOKUP 函数就无法处理了。

这时候，我们可以组合使用 MATCH 函数和 OFFSET 函数。为了导出 E2中“No.”所对应的单价数据，G2中要输入以下公式：

=OFFSET(B1,MATCH(E2,B:B,0)-1,-1)

在单元格 G2中输入=OFFSET(B1,MATCH(E2,B:B,0)-1,-1)

以单元格 B1为基准，作为第二参数的结果的数字向下、再向左移动1格的目标单元格数值将会出现在 G2中。

第二参数的 MATCH 函数，会查找单元格 E2的值位于 B 列的上数第几列。单元格 E2的值若为1，B 列内容为1的单元格位于第2行，因此 MATCH 函数导出结果为“2”。在这个例子中，以单元格 B1为基准的 OFFSET 函数直接嵌入 MATCH 函数中，由于 B1向下偏移数为2，产生了1格的误差，所以需要做出调整，在此基础上减去1。

在 OFFSET 函数中，可以将第二参数的移动行数、第三参数的移动列数指定为负数值。也就是说，可以引用位于基准单元格的上方、左侧的单元格。利用这一特性，可以解决 VLOOKUP 函数无法引用位于检索列左侧单元格的缺陷。