Excel深度教學：VLOOKUP查表函數及比對中文字串時發生錯誤的問題

利用VLOOKUP查表公式，尋找其它欄位的數值

當我們利用「資料驗證」製作好文具用品分類的下拉式選單，也製作好第二層「品名」的動態下拉式選單之後，請參考「Excel深度教學：「二層」、「三層」的動態「下拉式選單」設定」，再來就是要傷腦筋每一項文具產品所對應的「單位」及「單價」了，總不能製作好便利的下拉式選單之後，卻要自己逐一的輸入各文具產品的「單位」及「單價」吧？

因此在這邊就要介紹一個在Excel中，非常好用的高階函式，也是「VLOOKUP」。

以下是微軟官方網站「如何使用 VLOOKUP 或 HLOOKUP 尋找完全相同的值」，對於VLOOKUP的參數資料，而括號內的文字是我的註解：

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

• Lookup_value：在陣列第一欄中所要尋找的值。(要用來比對的文字)
• Table_array：查閱資料中的資訊表格。(用來「被」比對的資料庫)
• col_index：相符數值應該傳回的 table_array 應傳回相符的值。(比對後，要傳回資料庫的哪個欄位，從1開始)
• Range_lookup：無論您想要尋找完全相符或近似的相符值，其為指定的邏輯值。如果是 TRUE 或是省略，就會傳回近似的相符值；也就是說，如果沒有找到完全相符的值，就會傳回下一個小於 Lookup_value 的最大值。如果是 FALSE，VLOOKUP 則會尋找完全相符值。如果找不到完全相同的值，就會傳回 #N/A 錯誤值。

看完了這些微軟的拉拉喳喳說明後，總之就是「要查什麼？從哪裡查？傳回什麼資料？」，接下來直接做個實例應用給大家看，就可以知道這個公式要如何運用了。

「VLOOKUP」函數範例說明

首先在使用VLOOKUP之前，我們要先把要查表的「表格」給準備好，這是一定的，因為您想要查表，總是要先有一個「表」來查吧？

Step 1. 以我們一直以來的文具用品的例子來說，我另外建立了一個「售價表」的工作表，如下圖所示，而我們所有使用的查表範圍即為紅色框框的地方，你知道為什麼要省略「分類」及「流水號」嗎？

這是因為VLOOKUP的函式規定，所要查詢的表格第一欄，就是我們所要比對的數值，因此我們就要把「品名」這個將要用來比對的資料，安排在第一欄的位置。

Step 2. 接下來就要在要輸入的表格中，設定VLOOKUP的公式了，請點擊「單位」下面的儲存格，接著輸入「=VLOOKUP(C2，售價表!C1:E10, 2, FALSE)」，公式中的說明如下：

C2 => 為參考值，在這為「鉛筆」，我們就是要利用這個字串，來查詢「表格」對應該欄位資料。
售價表!C1:E10 => 為資料庫的表格，要查詢的資料，都是從這邊來查詢的，也就是在上一步中的表格資料範圍。
2 => 是要回傳「表格」的欄位。(1為品名、2為單位、3是單價)，因此這邊會回傳「單位」這個欄位的值。
FALSE => 比對時，必需要完全相同，才回傳資料。

而您應該會注意到，我所輸入的公式，好像和我剛才說的不太一樣：

• 「=VLOOKUP(C2，售價表!C1:E10, 2, FALSE)」 <= 我剛才所說的公式
• 「=VLOOKUP(C2，售價表!C$1:E$10, 2, FALSE)」 <= 我實際輸入的公式

這兩個差別在於「$錢號」，稍微懂Excel的朋友應該知道，「$」在拖曳儲存格複製公式時，可以「固定」行號或列號，這我們等一下再示範。

比對中文字會發生錯誤的問題

在這裡我要提出一個問題：

「=VLOOKUP(C2，售價表!C1:E10, 2)」，若您在輸入公式時，少掉「FALSE」時，會發生什麼狀況呢？

由於少了FALSE，在比對時，就會以相似的比率來決定回傳的數值，因此這在比對「中文字」時，就會出現問題，例如下圖，當我將公式的FALSE拿掉之後，居然單位出現的是「包」，而不是正確的「支」了。

其實，為了要百分百的抓出正確的數值，把「FALSE」加上去就對了。

Step 3. 回到我們輸入資料的地方，當我們把「$」放進欄位的地方，在使用「上、下」拖曳複製公式時，C2這個儲存格的位置就會自動的遞增，第三格變C3，以此類推，所以下面的儲存格，也能快速的填好資料，如下圖：

而以同樣的方法來填入「單價」的公式，如下：

• 「=VLOOKUP(C2，售價表!C$1:E$10, 3, FALSE)」

和之前「單位」的差別只差在回傳的欄位值，回傳「單價」時，要回傳第3欄，這是為什麼這裡要放「3」的原因。

同樣將「單價」下方的儲存格，也複製好公式，就可以完成我們所要製作的表格了。

利用「名稱管理員」可以將VLOOKUP做的更好

以上述的方式來使用「VLOOKUP函式」已經是非常好了，同樣的公式，還可以寫的更簡潔、更好維護，也就利用前幾篇Excel教學文章中所提供的「名稱管理員」。

只要您新增定義「售價表」這個「名稱」之後，如下圖：

在輸入VLOOKUP公式的表格範圍時，就不需輸入又臭又長的表格定義了，不僅如此，將來若是查表的「表格」資料有異動，您都只需要去修正「名稱管理員」的資料定義範圍即可，而不需要大工程的修正輸入工作表的所有儲存格，因此在使用VLOOKUP時，強烈建議一定要使用「名稱」來輔助。

若不曉得名稱要如何定義的話，請參考「Excel深度教學：使用「資料驗證」來製作「下拉式選單」」這篇文章。

Excel教學相關文章

• EXCEL實用技巧教學「樞紐分析表(Pivot Tables)」
• Excel深度教學：使用「資料驗證」來製作「下拉式選單」
• Excel深度教學：「二層」、「三層」的動態「下拉式選單」設定
• Excel深度教學：VLOOKUP查表函數及比對中文字串時發生錯誤的問題
• 讓Excel自動檢查儲存格中是否有輸入重複的資料