【Excel, Google Sheets】搜尋表格 填入資料 強大的 VLOOKUP, INDEX, MATCH

Excel 小蛙一直沒辦法練熟,通常只記得常用的幾個公式,隨著需求變化及增加,學的公式也多了起來,這篇小蛙要記錄 搜尋表格 填入資料 的利器 VLOOKUP 以及如何使用 INDEX + MATCH 達到同樣的功能。有分拆 Excel 的需求可以看上一篇喔!

小蛙今天遇到一個狀況是,Excel 裡面包含客戶名單及消費金額,需要將金額填入旁邊一份打亂過的名單內,之前都知道有 VLOOKUP 這個公式,但每次學完就忘了,今天這個需求正好可以使用 VLOOKUP 來完成,趁此機會把它弄清楚。更詳細的教學可參考 使用 VLOOKUP、INDEX 或 MATCH 尋找值 @ Microsoft

需求

如上述(可見下圖),希望可以透過公式將 F 欄的金額正確填上

AM JKLUbdm470InED4ddbcZqUi acC0D QWAUrqvQEM61PeWzD5jkFwt3CdImQjhDPGlBbQ3Q2J8aZ1UrtkoWCgNYtDrdL5aPsTI3MYEIw64rxWUKw9b48AZVU2eHFtUgmm7wU3JPeRVY2YE83aTMQHHLUCbEA=w570 h280 no?authuser=2 【Excel, Google Sheets】搜尋表格 填入資料 強大的 VLOOKUP, INDEX, MATCH

使用 VLOOKUP

我們希望將下圖中 F 欄的金額參照 B 欄後自動填上,首先到 F2 輸入 =VLOOKUP( 後開始輸入公式

VLOOKUP(E2, B2:C9, 2, FALSE)

E2    => 要查找參照的對象,這格是丁九,我們希望拿著 丁九 的名字去查金額
B2:C9 => 拿著 丁九 的名字到 B2:C9 儲存格內查找有沒有 丁九
2     => 如果查到 丁九 的名字後,要抓取的欄位,1 為 丁九 2 為 67657
FALSE => 必須要完全比對到 丁九 才行,多一個字少一個字都不行

VLOOKUP 的參數為 ( 查找對象, 查找範圍, 找到後第幾個欄位, 類似的要出現嗎 ),其實不用記也沒關係,大概知道一下 VLOOKUP 可以做到的事及一些些用法,在 Excel 裡面輸入 VLOOKUP 的時候,就會跳出提示了 (只是有實際做過或清楚一點會比較快可以寫出,不用每次都要查)

AM JKLUxiU rXe k6qmnxzxpDGdnyqXmj2D3SGNHXeVXlKrgi9QB7Xa3GyPbBcTxPnGhX2sFLyQYemDypTcCBhwToAgx5SnyGz0hFdKzMjlNm5eMxhQlaOPjdw4dpjrdcjVv1LNDjQLXv864kIeMhTs2Fw9P0A=w667 h382 no?authuser=2 【Excel, Google Sheets】搜尋表格 填入資料 強大的 VLOOKUP, INDEX, MATCH

噹啷,正確抓到 丁九 的消費金額

AM JKLXgGLXsIWNUDCbwhTngUDWC7aPh05MqYcJIH3r8JTTO6tDlvEHqOW6Iu7C0jEjMa1cdPsit222WVDig9S0jkcy0lTBHHi6Z8GNr Z hyDqVzTw1cE307VypcWbkgT Qxrln4e33a0DnTvi J350r9eSYw=w571 h275 no?authuser=2 【Excel, Google Sheets】搜尋表格 填入資料 強大的 VLOOKUP, INDEX, MATCH

往下拉填滿公式後發現,怎麼又出現討人厭的 #N/A

AM JKLX9zotRsu6Wb2M2F9bVfKTEnFYKuJjr6VVMzXiuwH0nRp1dQ2UEipgPuLlsygiKHFMg5ErUnMtnoc2OYP HVdwnSSl7IfGAefcLWbsqsNSiNCZPlaSK3hCjeOp4GwmaOYfx7EsCPJA0fm2llhIvXcq4jQ=w612 h384 no?authuser=2 【Excel, Google Sheets】搜尋表格 填入資料 強大的 VLOOKUP, INDEX, MATCH

查看 潘八 這格的比對範圍變成 B3:C10 而不是我們原本設定的 B2:C9,李四 的那格也變成 B4:C11,比對範圍逐漸偏移,導致有些名字比對不到

AM JKLVNrWTe g3tKyCxERfZRhoUttLGBcgHdHQQiZjnSV412hVNUg6X uKWZZ0MBHIkYeZPnOAYyeDKy i9W0S7GZg0N4C5PxKRL9UQP3rBV0fJvY2j5jqWgJvdTMErlS7lZLyFZG5A2yPF4743BA2fY5eHg=w651 h380 no?authuser=2 【Excel, Google Sheets】搜尋表格 填入資料 強大的 VLOOKUP, INDEX, MATCH

Excel 裡面有一個超頑固的好朋友 $ 可以用來「固定」住欄或列,不會隨著填滿公式而自動遞增,再試一次就成功囉!每個人的名字都填上了正確的消費金額。

AM JKLUXzymp0ZaOE Ap JgMXACLKytvpQiJfIcXmIbp1fXRMcheK4lcuUStIi0jFC3 TChpFociyHjKm1H4Gb6HMlq9Yy BUtpQv6rhe 00ufDMfiTnQD DBph u 0oYlcMoTExTx fHm7aoIFPQSxk5SopQg=w644 h383 no?authuser=2 【Excel, Google Sheets】搜尋表格 填入資料 強大的 VLOOKUP, INDEX, MATCH

使用 INDEX + MATCH

使用 VLOOKUP 有一些限制,VLOOKUP 函數只能從左至右尋找值。 這表示包含您尋找之值的欄應一直位於包含退貨值的欄左側。 如果您的試算表不是以這種方式建立,請不要使用 VLOOKUP。 請改為使用 INDEX 和 MATCH 函數的組合。

from 使用 VLOOKUP、INDEX 或 MATCH 尋找值

INDEX + MATCH 也可以做到一樣的事情,多學一點如果真的遇到 VLOOKUP 不能處理的狀況,還是能安全下莊,這個公式就長了一點,不過仔細看其實感覺上是差不多的。

MATCH(E2, B$2:B$9, 0)

E2      => 要查找參照的對象,這格是丁九,我們希望拿著 丁九 的名字去查
B$2:B$9 => 拿著 丁九 的名字到 B2:B9 儲存格內查找有沒有 丁九
0       => 要完全符合 丁九 的名字才行


INDEX(B$2:C$9, MATCH(), 2)
B$2:C$9 => 要比對與取得的範圍
MATCH() => 上面的 MATCH(E2, B$2:B$9, 0)
2       => B$2:C$9 的第幾個欄位

完整公式
INDEX(B$2:C$9, MATCH(E2, B$2:B$9, 0), 2)
AM JKLUl3YPdC0c9iQIrSxcerQLSY0KwwAgKe7GOPiAb aDvbYJiUaeWcDZ4XPLH9DgFWPp1RyKcAhNEpzNnqQMbMJdni4prxqkzp3iJqXK14KSIL64WzSyjJHpuWroPBcxgBboZXlKr94qdAgou B6ZiAcAjg=w714 h379 no?authuser=2 【Excel, Google Sheets】搜尋表格 填入資料 強大的 VLOOKUP, INDEX, MATCH

Excel 相關文章:

    發佈留言

    發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

    這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料