輸入表與負(fù)責(zé)部分的項(xiàng)目順序不同時(shí)

在剛才的例子中，為了讓“輸入表”與“負(fù)責(zé)部分”的項(xiàng)目排列順序保持一致，第三參數(shù)按順序輸入2、3、4……這樣連續(xù)的序號。因此，VLOOKUP 函數(shù)第三參數(shù)引用嵌入了 COLUMN 函數(shù)，這樣做會(huì)提高效率。

但是，如果像下面這樣，輸入表與負(fù)責(zé)部分的項(xiàng)目順序不同時(shí)該怎么辦？也就是說第三參數(shù)不是連續(xù)數(shù)字的話，各單元格中的 VLOOKUP 函數(shù)即便運(yùn)用了 COLUMN 函數(shù)，也無法得出正確的第三參數(shù)。

輸入表與負(fù)責(zé)部分的項(xiàng)目順序不同時(shí)

此例中，D 列的“單價(jià)”對應(yīng)“負(fù)責(zé)部分”最左端往右數(shù)第6列，E 列的“生產(chǎn)者”對應(yīng)“負(fù)責(zé)部分”最左端往右數(shù)第5列。在這樣的前提下，如果想要在單元格 C3中輸入最開始的那個(gè)函數(shù)公式，之后只要復(fù)制到 G 列也都可以得出結(jié)果的話，我們應(yīng)該怎么做呢？

在 C 列商品名的單元格輸入的 VLOOKUP 函數(shù)中第三參數(shù)應(yīng)該是什么數(shù)字呢？答案是2。那么，我們只要輸入能自動(dòng)導(dǎo)出數(shù)字2的第三參數(shù)就可以了。這時(shí)候，我們就要用到 MATCH 函數(shù)。

我們通過以下例子具體解釋一下。

上述例子中，A1到 D1項(xiàng)目名稱分別為“商品名”、“單價(jià)”、“生產(chǎn)者”、“最低訂購單位”，這些項(xiàng)目在 F1到 I1的范圍中位于左數(shù)第幾列，會(huì)相應(yīng)地顯示在 A2到 D2中。以單元格 A2為例，“A1（即商品名）的值，在 F1：I1范圍里位于左數(shù)第2個(gè)”，那么 A2中則會(huì)顯示數(shù)字2。

在單元格 A2做出這種處理的是下面的函數(shù)公式。

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

MATCH 函數(shù)中第一參數(shù)指定的值，會(huì)導(dǎo)出在第二參數(shù)指定范圍中位于第幾位的數(shù)字。第三參數(shù)基本上“只要輸入0就行了”。

在圖中，將單元格 A2的公式一直復(fù)制粘貼到 D2。因?yàn)榈谝粎��?shù)不做絕對引用，單元格 B2里被復(fù)制粘貼的公式中的第一參數(shù)為 B1，單元格 C2里被復(fù)制粘貼的公式的第一參數(shù)為 C1，D2中則是 D1。

第二參數(shù)限定了縱列或橫行的范圍。

▲指定縱列的范圍

第一參數(shù)指定的值為在此范圍內(nèi)的上數(shù)第幾行。

▲指定橫行的范圍

第一參數(shù)指定的值為在此范圍內(nèi)左數(shù)第幾列。

單元格范圍限定為 F1:I1，則呈現(xiàn)如下狀態(tài)：

• 單元格 A1即“商品名”位于左數(shù)第2個(gè)
• 單元格 B1即“單價(jià)”位于左數(shù)第4個(gè)
• 單元格 C1即“生產(chǎn)者”位于左數(shù)第3個(gè)
• 單元格 D1即“最低訂購單位”位于左數(shù)第1個(gè)

能夠在單元格中顯示數(shù)字2、4、3、1，是因?yàn)?MATCH 函數(shù)的處理。

在 VLOOKUP 函數(shù)的第三參數(shù)中加入 MATCH 函數(shù)，即使“輸入表”與“負(fù)責(zé)部分”的項(xiàng)目的排列順序不同，也能夠通過 MATCH 函數(shù)取得“‘輸入表’的各項(xiàng)目名在‘負(fù)責(zé)部分’下位于第幾列”的數(shù)字，把這樣的結(jié)構(gòu)嵌入 VLOOKUP 函數(shù)第三參數(shù)中就能夠解決順序不同的問題。在輸入表的單元格 C3，請輸入以下公式：

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

然后復(fù)制到整個(gè)表格，畫面則顯示如下：

在單元格 C3中輸入=VLOOKUP($B3,$I:$N,MATCH(C$2,$I$2:$N$2,0),0)并復(fù)制粘貼至全表

分析 MATCH 函數(shù)的處理

可能乍一看上述的公式很復(fù)雜，接下來我們來仔細(xì)分析一下。關(guān)鍵在于理解嵌入 VLOOKUP 函數(shù)第三參數(shù)的 MATCH 函數(shù)是如何發(fā)揮作用的。

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

這個(gè)公式得出的數(shù)字指向的是，第一參數(shù)指定的單元格 C2的值（即商品名的值）位于第二參數(shù)指定范圍（$I$2:$N$2）的左數(shù)第幾個(gè)。在這一例子中為數(shù)字2，它與單元格 C3中以 B3的值（數(shù)字1）為檢索值的 VLOOKUP 函數(shù)里，檢索范圍 I:N 從左數(shù)第幾列的對應(yīng)數(shù)字是一致的。

將輸有單元格 C3內(nèi)容的單元格一致復(fù)制粘貼到 G6，為了不讓參照項(xiàng)移位，需要設(shè)定絕對引用。