[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)


第三個參數:
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()確實略勝一籌。接下來我們看看如果對照表是水平的呢?

準備一個水平對照表:

等級
獎學金0500100015002000

 

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

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

以往看到需要查詢水平對照表就只能使用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()這個新函數了!

 

相關文章:職場使用頻率最高的VLOOKUP、HLOOKUP函數詳解


 

YiruAtStudio - 關於我 - 意如