使用 VLOOKUP、INDEX 或 MATCH 尋找值

您有一份辦公室號碼清單,而且必須知道哪個員工在哪個辦公室。但是試算表非常龐大,您該怎麼辦? 使用查閱函數。VLOOKUPHLOOKUP 函數是兩種最實用的函數,而 INDEXMATCH 也很實用。

附註:  如果您正在尋找 [查閱精靈],該功能已不包含在 Excel 中。

以下提供如何使用 VLOOKUP 的快速提醒。

=VLOOKUP(B2,C2:E7,3,TRUE)

第一個引數是函數運作所需的資料,也就是您要尋找的值。該值可以是儲存格參照或是如 "smith" 或 21,000 此類的固定值。第二個引數是您認為包含要尋找之值的儲存格範圍。在此範例中,則為 C2-C7。第三個引數是包含您要查看之值的儲存格範圍中的欄位。

第四個引數為選用。您可以輸入 True 或 False。如果您輸入 TRUE,或將引數留白,函數會傳回您在第一個引數中指定的大約符合值。如果您輸入 FALSE,則函數會符合第一個引數提供的值。換言之,將第四個引數留白,或輸入 TRUE,會更有彈性。

此範例顯示函數如何運作。當您在儲存格 B2 (第一個引數) 輸入值時,VLOOKUP 會搜尋儲存格 C2-E7 (第二個引數) 並傳回範圍中第三欄的最接近的大約符合值,也就是欄 E (第三個引數)。

VLOOKUP 函數的常見用法

第四個引數為空白,因此函數傳回大約符合值。如果未傳回,您必須輸入欄 C 或 D 的其中一個值以取得結果。

一旦您熟悉 VLOOKUP 後,HLOOKUP 函數應該不會太難上手。您可以輸入相同的引數,但會尋找列而不是欄中的值。

小試身手

如果您在將查閱函數運用在資料上之前,想先試用這些函數,這裡有一些範例資料供您使用。有些人喜歡使用 VLOOKUP 和 HLOOKUP,有些人則偏好同時使用 INDEX 和 MATCH。您可以嘗試每種方式,找出最喜歡的方法。

VLOOKUP 作用中

複製這個資料表中的所有儲存格,並貼到 Excel 空白工作表的 A1 儲存格中。

提示    將資料貼入 Excel 之前,請先將 A 欄到 C 欄的欄寬度設為 250 像素 ,並按一下 [自動換列] ([常用] 索引標籤、[對齊方式] 群組)。

密度

黏度

溫度

0.457

3.55

500

0.525

3.25

400

0.606

2.93

300

0.675

2.75

250

0.746

2.57

200

0.835

2.38

150

0.946

2.17

100

1.09

1.95

50

1.29

1.71

0

公式

描述

結果

'=VLOOKUP(1,A2:C10,2)

在 A 欄中尋找大約符合 1 的值,在 A 欄中找到一個小於或等於 1 的最大值 (0.946),再傳回 B 欄中同一列的值。

=VLOOKUP(1,A2:C10,2)

'=VLOOKUP(1,A2:C10,3,TRUE)

在 A 欄中尋找大約符合 1 的值,在 A 欄中找到一個小於或等於 1 的最大值 (0.946),再傳回 C 欄中同一列的值。

=VLOOKUP(1,A2:C10,3,TRUE)

'=VLOOKUP(0.7,A2:C10,3,FALSE)

在 A 欄中尋找完全符合 0.7 的值。因為 A 欄中沒有完全符合的值,因此傳回錯誤。

=VLOOKUP(0.7,A2:C10,3,FALSE)

'=VLOOKUP(0.1,A2:C10,2,TRUE)

在 A 欄中尋找大約符合 0.1 的值。因為 0.1 小於 A 欄中的最小值,因此傳回錯誤。

=VLOOKUP(0.1,A2:C10,2,TRUE)

'=VLOOKUP(2,A2:C10,2,TRUE)

在 A 欄中尋找大約符合 2 的值,在 A 欄中找到一個小於或等於 2 的最大值 (1.29),再傳回 B 欄中同一列的值。

=VLOOKUP(2,A2:C10,2,TRUE)

HLOOKUP 作用中

複製這個資料表中的所有儲存格,並貼到 Excel 空白工作表的 A1 儲存格中。

提示    將資料貼入 Excel 之前,請先將 A 欄到 C 欄的欄寬度設為 250 像素 ,並按一下 [自動換列] ([常用] 索引標籤、[對齊方式] 群組)。

輪軸

軸承

螺栓

4

4

9

5

7

10

6

8

11

公式

描述

結果

'=HLOOKUP("輪軸", A1:C4, 2, TRUE)

在第 1 列中查詢 "輪軸",從同一欄 (A 欄) 的第 2 列傳回值。

=HLOOKUP("輪軸", A1:C4, 2, TRUE)

'=HLOOKUP("軸承", A1:C4, 3, FALSE)

在第 1 列中查詢 "輪軸",從同一欄 (B 欄) 的第 3 列傳回值。

=HLOOKUP("軸承", A1:C4, 3, FALSE)

=HLOOKUP("B", A1:C4, 3, TRUE)

在第 1 列中查詢 "B",從同一欄的第 3 列傳回值。由於找不到與 "B" 完全相符的項目,因此使用第 1 列中小於 "B" 的最大值:A 欄 中的 "輪軸"。

=HLOOKUP("B", A1:C4, 3, TRUE)

'=HLOOKUP("螺栓", A1:C4, 4)

在第 1 列中查詢 "螺栓",從同一欄 (C 欄) 的第 4 列傳回值。

=HLOOKUP("螺栓", A1:C4, 4)

'=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)

在三列的常數陣列中查詢數字 3,並從同一欄 (在此例中為第三欄) 的第 2 列傳回值。常數陣列中有三列的值,每一列均以分號 (;) 區隔。由於在第 2 列找到 "c",而在同一欄是 3,因此會傳回 "c"。

=HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,TRUE)

INDEX 和 MATCH 作用中

這個範例同時使用 INDEX 和 MATCH 函數,傳回五個鄉/鎮/市/區中每個鄉/鎮/市/區最早的發票編號及其對應日期。因為日期會以數值的形式傳回,所以我們使用 TEXT 函數將其格式設定為日期。實際上,INDEX 函數會使用 MATCH 函數的結果作為引數。INDEX 和 MATCH 函數的組合在每個公式中會有兩次的運用——首先用於傳回發票編號,再用於傳回日期。

複製這個資料表中的所有儲存格,並貼到 Excel 空白工作表的 A1 儲存格中。

提示    將資料貼入 Excel 之前,請先將 A 欄到 D 欄的欄寬度設為 250 像素 ,並按一下 [自動換列] ([常用] 索引標籤、[對齊方式] 群組)。

發票

城市

發票日期

最早發票 (依城市) 與日期

3115

亞特蘭大

4/7/12

="亞特蘭大 = "&INDEX($A$2:$C$33,MATCH("亞特蘭大",$B$2:$B$33,0),1)& ", 發票日期: " & TEXT(INDEX($A$2:$C$33,MATCH("亞特蘭大",$B$2:$B$33,0),3),"m/d/yy")

3137

亞特蘭大

4/9/12

="奧斯丁 = "&INDEX($A$2:$C$33,MATCH("奧斯丁",$B$2:$B$33,0),1)& ", 發票日期: " & TEXT(INDEX($A$2:$C$33,MATCH("奧斯丁",$B$2:$B$33,0),3),"m/d/yy")

3154

亞特蘭大

4/11/12

="達拉斯 = "&INDEX($A$2:$C$33,MATCH("達拉斯",$B$2:$B$33,0),1)& ", 發票日期: " & TEXT(INDEX($A$2:$C$33,MATCH("達拉斯",$B$2:$B$33,0),3),"m/d/yy")

3191

亞特蘭大

4/21/12

="紐奧良 = "&INDEX($A$2:$C$33,MATCH("紐奧良",$B$2:$B$33,0),1)& ", 發票日期: " & TEXT(INDEX($A$2:$C$33,MATCH("紐奧良",$B$2:$B$33,0),3),"m/d/yy")

3293

亞特蘭大

4/25/12

="坦帕 = "&INDEX($A$2:$C$33,MATCH("坦帕",$B$2:$B$33,0),1)& ", 發票日期: " & TEXT(INDEX($A$2:$C$33,MATCH("坦帕",$B$2:$B$33,0),3),"m/d/yy")

3331

亞特蘭大

4/27/12

3350

亞特蘭大

4/28/12

3390

亞特蘭大

5/1/12

3441

亞特蘭大

5/2/12

3517

亞特蘭大

5/8/12

3124

奧斯丁

4/9/12

3155

奧斯丁

4/11/12

3177

奧斯丁

4/19/12

3357

奧斯丁

4/28/12

3492

奧斯丁

5/6/12

3316

達拉斯

4/25/12

3346

達拉斯

4/28/12

3372

達拉斯

5/1/12

3414

達拉斯

5/1/12

3451

達拉斯

5/2/12

3467

達拉斯

5/2/12

3474

達拉斯

5/4/12

3490

達拉斯

5/5/12

3503

達拉斯

5/8/12

3151

紐奧良

4/9/12

3438

紐奧良

5/2/12

3471

紐奧良

5/4/12

3160

坦帕

4/18/12

3328

坦帕

4/26/12

3368

坦帕

4/29/12

3420

坦帕

5/1/12

3501

坦帕

5/6/12

深入了解查閱函數

頁面頂端

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×