ฟังก์ชัน XLOOKUP ใน Excel พร้อมตัวอย่างการใช้งาน

Function XLOOKUP

ฟังก์ชัน XLOOKUP ใช้สำหรับการค้นหาค่าจากตารางโดยระบุว่าจะนำค่าอะไร ไปหา แล้วจะได้ข้อมูลใดกลับคืนมา เช่น ต้องการหา ชื่อสินค้า จาก รหัสสินค้า เป็นต้น

สูตรนี้มีความคล้ายกับฟังก์ชัน VLOOKUP แต่มีความยืดหยุ่นมากกว่าสามารถค้นหาได้ทั้งตารางในลักษณะแนวตั้ง และแนวนอน รวมความสามารถของ IFERROR เข้ามาด้วย และยังสามารถค้นหาแบบ Binary Search ได้อีกด้วย และมีการคำนวณที่มีประสิทธิภาพดีกว่า ทำให้ไฟล์เล็ก และ ทำงานได้รวดเร็วกว่า

สำหรับใครที่ใช้งาน VLOOKUP แล้วรู้สึกไฟล์ช้า ลองปรับมาเป็น XLOOKUP จะเห็นผลของการทำงานว่า ไฟล์ขนาดเล็กลง และทำงานได้เร็วขึ้น ไม่หน่วงเครื่อง

รูปแบบของ Syntax

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

lookup_value คือ ค่าที่เราใช้ค้นหา
lookup_array คือ ช่วงข้อมูลหรือ array ที่จะค้นหา
return_array คือ ช่วงข้อมูลหรือ array ที่จะคืนค่า
[if_not_found] คือ ค่า หรือ การคำนวณที่จะให้ทำงาน ในกรณี ไม่พบข้อมูล โดยหากไม่ระบุค่านี้แล้ว หากไม่พบ จะคืนค่ากลับเป็น #N/A
[match_mode] คือ กำหนดค่าจะให้เรียงลำดับในกรณีมีค่าเท่ากันอย่างไร โดยมีรูปแบบด้วยกัน 2 ลักษณะ คือ

[search_mode] คือ กำหนดค่าโหมดของการค้นหา โดยมีรูปแบบด้วยกัน 2 ลักษณะ คือ

การคืนค่า

ผลลัพธ์เป็นค่า 1 ค่า (single scalar value)

ตัวอย่างการใช้งาน XLOOKUP

ตัวอย่างการใช้งาน XLOOKUP ทั่วไป

การหาชื่อพนักงานโดยใช้รหัสพนักงาน สามารถทำได้ดังแสดงในรูปนี้

ซึ่งจะแสดงสูตรคำนวณดังแสดงนี้
=XLOOKUP(F9,C1:C8,D1:D8) ซึ่งก็คือ =XLOOKUP(หาอะไร,จากที่ไหน,ผลลัพธ์)

ซึ่งหากเป็น VLOOKUP จะเป็นรูปแบบนี้
=VLOOKUP(F9,C1:D8,3,FALSE)

การกำหนดค่าเมื่อ XLOOKUP แล้วไม่พบค่า

สามารถกำหนดค่า not_found ในสูตร XLOOKUP ได้เลย ตัวอย่างเช่น Cell G8 ใส่สูตรว่า
=XLOOKUP(F9,C1:C8,D1:D8,"Not Found")
ตัวอย่างในภาพ หากทำการ Enter จะแสดงข้อความว่า Not Found สำหรับการนำเอารหัส 22008 ไปหาในตาราง ตามภาพนี้

การค้นหาแบบ Wildcard ในฟังก์ชัน XLOOKUP

เป็นความสามารถในการค้นหาโดยระบุเพื่อค้นหาโดยระบุค่าบางส่วนของการค้นหา โดยมีสัญลักษณ์ที่ใช้งานได้คือ
? แทน ตัวอักษรใด ๆ 1 ตัวอักษร เช่น ใส่คำว่า EXP?RT ก็จะได้ทั้ง EXPERT หรือ EXPORT
* แทน ตัวอักษรใด ๆ กี่ ตัวอักษร ก็ได้ เช่น ใส่คำว่า *ราช* ก็จะได้ทั้ง ราชบุรี หรือ อุบลราชธานี หรือ นครศรีธรรมราช
~ ใช้ต่อกับ ?, * หรือ ~ เพื่อทำการบอกว่าเป็นคำว่า ?, * หรือ ~ ตัวอย่างเช่น ok~? จะได้ ok?

ตัวอย่างการใช้ Wildcard เพื่อทำการค้นหารายชื่อพนักงานโดยรู้แค่คำนำหน้า

สูตรที่ใส่ใน G9 คือ =XLOOKUP(F9,C1:C8,D1:D8,,2)
โดยกำหนด match_mode เป็น 2 ซึ่งคือการใช้ Wildcard โดยกำหนดว่า Ray* ซึ่งหมายถึงขึ้นต้นด้วย Ray*
ได้ผลลัพธ์ คือคำว่า Sales คือจะได้คนแรกที่มีคำว่า Ray

ประโยชน์ของ XLOOKUP เมื่อเทียบกับ VLOOKUP

1. ทำงานได้เร็วกว่า
2. ไฟล์มีขนาดเล็กกว่า (จะเห็นเมื่อข้อมูลมีจำนวนมาก ๆ)
3. ยืดหยุ่นกว่า คือ สามารถหาได้ทั้ง ข้อมูลแนวตั้ง (Vertical) และ แนวนอน (Horizontal) อีกทั้งเริ่มการหาจากด้านบน หรือ ด้านล่างของข้อมูล ซึ่งส่งผลต่อเรื่องประสิทธิภาพการค้นหาอีกด้วย
4. สามารถหาแบบ Wildcard ทำให้สามารถหาส่วนหนึ่งส่วนใด ขึ้นต้นด้วย หรือ ลงท้ายด้วย คำที่ต้องการได้
5. สามารถใส่ค่า ในกรณีหาไม่พบ ได้ แต่หาก VLOOKUP จำเป็นจะต้องใช้คู่กับ IFERROR

เวอร์ชั่นที่รองรับ XLOOKUP

ต้องเป็น Microsoft Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel for Android phone

 

Download

ไฟล์ตัวอย่างการใช้งาน ฟังก์ชัน XLOOKUP

 

อ้างอิงจากเว็บไซต์ Microsoft

XLOOKUP function - Office Support (microsoft.com)

 

แนะนำหลักสูตรการอบรม

เรียนรู้คำสั่งต่าง ๆ พร้อมการประยุกต์ใช้งานในเชิงธุรกิจ ได้จากหลักสูตร
Microsoft Excel Advanced