菜鳥救星Excel教學:最新函數XLOOKUP取代VLOOKUP、HLOOKUP?

文、意如 老師

用了一輩子真心覺得好用的VLOOKUP()跟HLOOKUP(),要被新函數XLOOKUP()取代了嗎?

XLOOKUP()函數目前只在office365(含)以上版本才有支援,所以要使用之前得先更新。

最近試用了一陣子覺得真的很方便,先跟大家分享一下XLOOKUP()函數怎麼使用後,我們再來討論是否真的可以取代掉舊的VLOOKUP()、HLOOKUP()函數。

XLOOKUP( )使用方式?

XLOOKUP公式如下:

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_model])

lookup_value = 要尋找的資料
lookup_array = 尋找的範圍
return_array = 要回傳的範圍
[if_not_found] = 如果找不到要顯示的內容(可省略)
[match_mode] = 配對模式(可省略)
[search_model] =查詢模式(可省略)

XLOOKUP(1.要尋找的資料,2.尋找的範圍,3.要回傳的範圍,[如果找不到要顯示的內容,配對模式,查詢模式])


看得有點矇沒關係~我們馬上開始實作看看。

1. 準備對照表,填入Excel

學區 地址 電話
忠孝 南路一段一二四號 39123456
基隆 和平東路三段一巷一號 29461234
館前 五權南路九十九號 39985621
新莊 興楠路一八0號 29457845
板橋 五權南路九十九號 39112356
永和 中山路一七0號 29951365
士林 鼓山區明誠三路五八六號 39214754
三重 民權路一二七號 29213652
公館 博愛路一三一號 39124785
羅東 士東路一九○號 29569521

2. 接下來我們要找的是學生報名的分校地址,並填入Excel

姓名 報名 地址(XLOOKUP)
賴驗新 基隆  
蔡小燕 公館  
鄭新明 板橋  
陳智與 三重  
黃麗倩 忠孝  
沈樂豐 板橋  
周凱明 忠孝  
吳美足 三重  

3. G2儲存格輸入函數 = XLOOKUP(

4. 輸入參數

第一個參數:
lookup_value = 要尋找的資料
目前要尋找的參數是 F2(基隆)
G2輸入=XLOOKUP(F2,

第二個參數:
lookup_array = 尋找的範圍

目前要尋找的範圍為A2:A11,因為等等要往下複製,所以加上固定符號”$”字號 $A$2:$A$11

G2 輸入= XLOOKUP (F2, $A$2:$A$11,


第三個參數:
return_array = 要回傳的範圍

目前要回傳的範圍是地址所以為B2:B11,因為等等要往下複製,所以加上固定符號”$”字號 $B$2:$B$11

G2完整公式如下:
= XLOOKUP (F2, $A$2:$A$11, $B$2:$B$11)

與VLOOKUP( )比較?

如果跟剛剛上面做一樣的事情

XLOOKUP()的公式如下:
=XLOOKUP(F2,$A$2:$A$11,$B$2:$B$11)

VLOOKUP()的公式如下:
=VLOOKUP(F2,$A$2:$C$11,2,0)

大家有沒有發現到,VLOOKUP必須寫入第四個參數告知,(0或1),0找到完全符合的值,1為最接近的值。

就以這範例來說XLOOKUP()確實略勝一籌。接下來我們看看如果對照表是水平的呢?

準備一個水平對照表:

等級
獎學金 0 500 1000 1500 2000

下列是我們要查詢什麼樣的等級可以領到多少獎學金的表:

姓名 等級 獎學金
賴驗新  
蔡小燕  
鄭新明  
陳智與  
黃麗倩  

以往看到需要查詢水平對照表就只能使用HLOOKUP()函數,現在我們要用XLOOKUP()函數來實作看看。

輸入第一個參數:
查找值: =XLOOKUP(B7)

第二個參數:
我要找對照的儲存格B1:F1,因為等等要往下複製,所以這裡可按快速鍵(F4)加入$字號,$B$1:$F$1

第三個參數就是我們要抓的範圍獎學金,B2:F2 ,一樣需要加上$字號,$B$2:$F$2

最後往下複製,完整公式:
=XLOOKUP(B7,$B$1:$F$1,$B$2:$F$2)

與HLOOKUP()比較?

如果跟剛剛上面做一樣的事情

XLOOKUP()的公式如下:
=XLOOKUP(B7,$B$1:$F$1,$B$2:$F$2)

HLOOKUP()的公式如下:
=HLOOKUP(B7,$B$1:$F$2,2,0)

大家一定有發現到,HLOOKUP()跟VLOOKUP()都是必須寫入第四個參數告知,(0或1)。

整體來講XLOOKUP()它可以用來查詢垂直對照表,也可用來查詢水平對照表,等於一函數底兩用,而在使用過程方便簡潔多了。

我在想應該不用多久,陪伴我們一輩子好用的VLOOKUP()和HLOOKUP()真的有很大的機會可能會被淘汰掉,而轉用XLOOKUP()這個新函數了!

加入菜鳥救星官方Line並回覆「我想看文章」,不定期獲得更多知識吧!

不同頻道,不同知識!

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