提示: 請嘗試使用新的 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 (第 2 個引數) 中的儲存格,並會從範圍的第三欄 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 HTTPs://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Bill Jelen 所寫的文章。

小試身手

如果您想要先嘗試使用尋找函數,然後再嘗試自己的資料,以下是一些範例資料。

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

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×