Applies ToMicrosoft 365 Excel Excel 網頁版 Excel 2024 Excel 2021 Excel 2019 Excel 2016

提示: 請嘗試使用新 XLOOKUPXMATCH 函數,這是本文所述函數的改良版本。 這些新函數可朝任何方向運作,預設會傳回完全相符的專案,讓它們比前置函數更容易且更方便使用。

假設您有一份辦公室位置號碼清單,而且您必須知道每個辦公室有哪些員工。 電子錶格非常龐大,因此您可能會認為這是一項具挑戰性的工作。 使用查閱函數其實很容易就能完成。

VLOOKUPHLOOKUP 函數以及 INDEXMATCH 是 Excel 中一些最實用的函數。

附註: Excel 已不再提供 [查閱精靈] 功能。

以下是如何使用 VLOOKUP 的範例。

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

在此範例中,B2 是第一個 自變數,這是函數需要運作的數據元素。 對於 VLOOKUP,第一個自變數是您要尋找的值。 此自變數可以是單元格參照,或是固定值,例如 「smith」 或 21,000。 第二個自變數是要在其中搜尋要尋找之值的單元格範圍 C2-:E7。 第三個自變數是該儲存格範圍中包含您所搜尋之值的欄。

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

此範例顯示函數如何運作。 當您在單元格 B2 中輸入值 (第一個自變數) 時,VLOOKUP 會) 搜尋範圍 C2:E7 (2nd 自變數中的單元格,並傳回範圍中第三欄的最接近的大約符合值,欄 E (第三個自變數) 。

VLOOKUP 函數的常見用法

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

當您熟悉 VLOOKUP 時,HLOOKUP 函數同樣容易使用。 您可以輸入相同的自變數,但會搜尋列而不是欄。

使用 INDEX 和 MATCH,而非 VLOOKUP

使用 VLOOKUP 有一些限制—VLOOKUP 函數只能從左到右查詢值。 這表示包含您查閱之值的欄應一律位於包含傳回值的欄左側。 現在,如果您的電子錶格不是以這種方式建立,則請不要使用 VLOOKUP。 請改用 INDEX 和 MATCH 函數的組合。

此範例顯示一份小型清單,其中包含我們所要搜尋的值 (芝加哥),但這並非位於最左邊的欄。 因此,我們無法使用 VLOOKUP。 我們將改用 MATCH 函數在範圍 B1:B11 中尋找「芝加哥」。 我們在列 4 中找到該值。 接著,INDEX 函數會將該值作為查閱引數,然後在第 4 欄 (欄 D) 中尋找芝加哥的人口。 所使用的公式顯示於儲存格 A14 中。

使用 INDEX 和 MATCH 來查閱值

如需使用 INDEX 和 MATCH 而非 VLOOKUP 的詳細範例,請參閱 Microsoft MVP bill Jelen https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ 一文。

小試身手

如果您想要在使用自己的數據試用查閱函數之前先嘗試查閱函數,以下是一些範例數據。

工作時的 VLOOKUP 範例

將下列資料複製到空白電子錶格。

提示: 將數據貼到 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 欄中同一列的值。

2.17

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

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

100

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

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

#N/A

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

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

#N/A

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

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

1.71

HLOOKUP 範例

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

提示: 將數據貼到 Excel 之前,請先將 A 欄到 C 欄的欄寬設定為 250 像素, 然後按兩下 [常用] 索引 卷標、 [對齊方式 ] 群組 ([自動換列]) 。

輪軸

軸承

螺栓

4

4

9

5

7

10

6

8

11

公式

描述

結果

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

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

4

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

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

7

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

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

5

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

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

11

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

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

c

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

快速參考卡:VLOOKUP

查閱與參照函數 (參照)

在 VLOOKUP 函數中使用table_array

需要更多協助嗎?

想要其他選項嗎?

探索訂閱權益、瀏覽訓練課程、瞭解如何保護您的裝置等等。

社群可協助您詢問並回答問題、提供意見反應,以及聆聽來自具有豐富知識的專家意見。