การใช้ฟังก์ชัน XLOOKUP ใน Excel

ภาพรวม

XLOOKUP ใช้ค้นหาค่าที่เราสนใจใน range หรือ table จากอีกค่าหนึ่งที่เรารู้อยู่แล้ว (keyword) โดยเปรียบเทียบง่าย ๆ ก็เหมือนเลขในบาร์โค้ด เมื่อเราสแกนแล้วระบบจะดึงชื่อสินค้าและราคามาให้เราเห็นนั่นแหละ

ถ้าใน Excel จะมีฟังก์ชันในกลุ่มนี้อยู่หลายตัว แต่ตัวใหม่ล่าสุดคือ XLOOKUP นี่แหละ ซึ่ง Microsoft บอกว่า ทำงานได้ไวกว่าด้วย

⚠️ โดยผู้ที่จะใช้งาน XLOOKUP ได้ จะต้องมี Microsoft 365 ใน Excel 2016 และ Excel 2019 ไม่มีให้ใช้ครับ

รูปแบบการใช้งาน (Syntax)

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

จำเป็น?	อาร์กิวเมนต์	คำอธิบาย
จำเป็น	lookup_value	ค่าที่เรารู้ และจะใช้ค้นหาค่าอื่น
จำเป็น	lookup_array	array หรือ range ที่คาดว่าจะมีค่าที่เราค้นหา
จำเป็น	return_array	array หรือ range ที่เราต้องการข้อมูล
ไม่ใส่ก็ได้	[if_not_found]	หากไม่พบ จะให้แสดงข้อความว่าอะไร? หากไม่ระบุจะได้เป็น #N/A
ไม่ใส่ก็ได้	[match_mode]	ระบุรูปแบบผลลัพธ์ที่อยากได้ 0 — ต้องตรงกันเป๊ะ ๆ หากไม่พบค่าที่กำหนดไว้ จะได้ที่เราระบุไว้ใน [if_not_found] หรือ #N/A หากไม่ใส่ XLOOKUP จะค้นหาด้วยแบบนี้โดยปริยาย (default)-1 — ต้องตรงกันเป๊ะ ๆ แต่หากไม่พบค่าที่กำหนดไว้ จะได้ค่าใกล้เคียงที่น้อยกว่ามาแทน1 — ต้องตรงกันเป๊ะ ๆ แต่หากไม่พบค่าที่กำหนดไว้ จะได้ค่าใกล้เคียงที่มากกว่ามาแทน2 — ค้นหาแบบใช้อักขระแทน โดยที่ ? * และ ~ จะมีความหมายเฉพาะ
ไม่ใส่ก็ได้	[search_mode]	ระบุรูปแบบการค้นหา โดยที่ 1 — ค้นหาเริ่มจากแถวแรกลงไป หากไม่ระบุจะใช้รูปแบบนี้โดยปริยาย (default)-1 — ค้นหาจากแถวสุดท้ายขึ้นมา2 — ค้นหาแบบทวิภาค 😂 อย่าไปสนใจคำนี้มากครับ เอาเป็นว่ามันจะเจอค่าได้เร็วกว่า หาก lookup_array เรามีข้อมูลเยอะ แต่การใช้วิธีนี้ lookup_array จะต้องเรียงลำดับจากน้อยไปหามากไว้แล้วเท่านั้น (ascending order)-2 — ค้นหาแบบทวิภาคเหมือนกัน เพียงแค่ lookup_array จะต้องเรียงลำดับจากมากไปหาน้อยไว้แล้วเท่านั้น (descending order)

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

เอาเหมือนที่เรายกตัวอย่างไว้ในตอนแรก สมมติว่าเรามีข้อมูลดังภาพนี้ โดยผมจะให้ข้อมูลนี้อยู่ในชีตที่ชื่อว่า products

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

=XLOOKUP(A3,products!$A$2:$A$10,products!$B$2:$B$10,"Not found!")

ในสูตรด้านบนนี้ ผมว่าข้อมูลสินค้าไว้ในชีตชื่อ products นะ เลยมีคำว่า products! อยู่หน้าตำแหน่งของเซลล์

ดูและดาวน์โหลด (File > Save As > Download a Copy) ไฟล์ตัวอย่างมาดูก็ได้ครับ

โดยบาร์โค้ดที่เราเลือกหรือป้อนเข้าไปจะอยู่ในเซลล์ A3 ส่วน lookup_array ที่เราจะเปรียบเทียบอยู่ชีตชื่อ products ตั้งแต่ A2 ถึง A10

หากเจอ XLOOKUP จะนำค่าที่อยู่ในแถวเดียวกันในคอลัมภ์ B (return_array) มาแสดง

ส่วนถ้าไม่พบ จะแสดงเป็นคำว่า Not found!

สำหรับการดึงราคาแสดง โดย return_array จะอยู่ในคอลัมภ์ C สูตรก็จะเป็นดังนี้

=XLOOKUP(A3,products!$A$2:$A$10,products!$C$2:$C$10,"Not found!")

ซึ่งผลลัพธ์ก็จะเป็นตามภาพนี้

ดูไว ๆ อาจดูเหมือนจะซับซ้อนกว่าการใช้ VLOOKUP อยู่พอสมควร นั่นเพราะ XLOOKUP จะยืดหยุ่นกว่าตรงที่จะหาค่าได้ทั้งด้านซ้ายและด้านขวาของคอลัมภ์ที่เราค้นหา (lookup_array)

ซึ่ง VLOOKUP ทำไม่ได้ return_array ต้องอยู่ขวามือเท่านั้น และจะค้นหาค่าที่เราระบุในคอลัมภ์แรกของ table_array เท่านั้น ลองดูตัวอย่างนี้

LOOKUP เอาข้อมูลที่อยู่ด้านซ้ายมือ

ข้อมูลที่ใช้จะเป็นคะแนนสอบของนักเรียน โดยจะทำเป็นตาราง (Format as Table) ชื่อว่า tblStudentScore ตามภาพด้านล่างนี้

แต่คราวนี้ เราบังเอิญอยากหาคะแนนจากชื่อเล่น (preferred_name) ของนักเรียน อย่างในภาพนี้

ซึ่งจะเห็นว่า ชื่อเล่น (preferred_name) ของนักเรียนในตารางข้อมูล อยู่ในคอลัมภ์ที่ 4 เลย

และสูตรที่เราใช้ในแต่ละคอลัมภ์จะเป็น

• student_id
  =XLOOKUP(A4,tblStudentScore[preferred_name],tblStudentScore[student_id],"Not found!",0,1)
• first_name
  =XLOOKUP(A4,tblStudentScore[preferred_name],tblStudentScore[first_name],"Not found!",0,1)
• last_name
  =XLOOKUP(A4,tblStudentScore[preferred_name],tblStudentScore[last_name],"Not found!",0,1)
• score
  =XLOOKUP(A4,tblStudentScore[preferred_name],tblStudentScore[score],"Not found!",0,1)

จากตัวอย่างข้างบนจะเห็นว่า เราได้ข้อมูลออกมาทุกอย่าง ซึ่งจะไม่เหมือน VLOOKUP ถ้าทำแบบนี้เราจะได้ #N/A หากต้องการผลลัพธ์แบบนี้ใน VLOOKUP เราจะต้องไปปรับโครงสร้างข้อมูลในตารางข้อมูลใหม่ โดยให้ชื่อเล่นมาอยู่ในคอลัมภ์แรก ซึ่งเป็นงานที่ไม่ค่อยสนุกเท่าไหร่ โดยเฉพาะไฟล์ที่ใช้ร่วมกันหลาย ๆ คน

หากใครอ่านสูตรข้างบนแล้วสงสัยว่า tblStudentScore คืออะไร ลองไปอ่านวิธีการ Format as Table เพิ่มนะครับ ผมชอบวิธีนี้ เพราะดูสตรที่เราเขียนแล้วมันเข้าใจง่ายดี

อีกอย่างคือ ถ้ามีการเพิ่มข้อมูลเข้าไป เราไม่ต้องมาแก้ไขสูตร เพื่อเพิ่มแถวเพิ่มคอลัมภ์

XLOOKUP ดึงข้อมูลล่าสุดจากตาราง

เป็นอีกอย่างหนึ่งที่ผมชอบใน XLOOKUP คือ เพียงเราเปลี่ยนอาร์กิวเมนต์ตัวเดียว เราก็จะได้ข้อมูลล่าสุดที่ต่อลงมาในตาราง โดยเฉพาะกรณีที่ใช้งานร่วมกับ Microsoft Forms ในที่ทำงาน ซึ่งเราสามารถ Sync ข้อมูลกับ Excel ได้เลย (เหมือน Google Forms กับ Google Sheets แหละ แต่เหมือน Microsoft พึ่งจะเปิดให้ใช้ไม่นานมานี้เอง 😒)

ในตัวอย่างนี้ ยกตัวอย่าง “รุ่ง” ซึ่งสอบได้ 56 คะแนน แล้วเราให้เขาสอบมาใหม่ สูตรที่จะใช้หาค่านี้ก็จะเป็น

=XLOOKUP(A13,tblStudentScore[preferred_name],tblStudentScore[score],"Not found!",0,-1)

ซึ่ง -1 หมายถึงให้ดูข้อมูลจากล่างสุดขึ้นมาตามที่อธิบายไว้ในรูปแบบการใช้งาน (syntax) ก็จะได้ผลดังภาพ

โดยถ้าเราดูจากข้อมูลจะเห็นว่า “รุ่ง” สอบรอบที่ 2 ได้คะแนน 78 คะแนน

LOOKUP ดึงข้อมูลที่มีค่ามากที่สุด หรือน้อยที่สุด

พออ่านมาถึงตรงนี้ บางคนอาจสงสัยว่า เอ… ถ้าเราเป็นครู/อาจารย์ที่ใจดีแบบสุด ๆ จะเอาคะแนนมากที่สุดที่สอบได้ทำไง?

คำตอบคือ เราจะไม่ใช้ทั้ง VLOOKUP และ XLOOKUP นะครับ

เราจะใช้ MAXIFS หรือไม่ก็ MINIFS แทน อย่างในภาพนี้

จากภาพด้านบน สูตรใน

• E18
  =MAXIFS(tblStudentScore[score],tblStudentScore[preferred_name],A18)
• E19
  =MINIFS(tblStudentScore[score],tblStudentScore[preferred_name],A19)

ส่วนการใช้ MAXIFS กับ MINIFS จะเหมือนกับ COUNTIFS ที่เคยเขียนไว้ก่อนหน้านี้ครับ

XLOOKUP แบบ binary search

💡กรณีนี้เหมาะสำหรับผู้ที่มีข้อมูลมาก ๆ ซึ่งจะเห็นผลว่าเร็วกว่าแบบธรรมดา อาจต้องมีข้อมูลเป็นหลายหมื่นแถวแหละ เร็วกว่าน่าจะในระดับมิลลิวินาที

ซึ่งการจะใช้วิธีค้นหาแบบนี้ ข้อมูลเราต้องถูกจัดลำดับค่าคีย์ (key) ที่จะค้นหาก่อน ไม่ว่าจะจากน้อยไปหามาก หรือกลับกัน เช่นข้อมูลสินค้าในตัวอย่างแรก เมื่อนำ barcode มาเรียงลำดับ โดยในที่นี้ผมใช้ SORTBY ดึงและนำมาเรียงจากน้อยไปหามาก และสูตรอยู่ในคอลัมภ์ E ดังภาพ

สูตรที่จะใช้ค้นหาก็เหมือนเดิมเลยครับ เพียงแค่เปลี่ยนอาร์กิวเมนต์ตัวสุดท้าย เป็นเลข 2 กรณีเรียงจากน้อยไปหามาก

=XLOOKUP(A4,products!$E$2:$E$10,products!$F$2:$F$10,"Not found!",0,2)

กลับหากเรียงจากมากไปหาน้อยก็จะต้องเป็นเลข -2 โดยผมทำไว้ให้ดูในไฟล์ตัวอย่างด้วย

เขียนไปเขียมมาชักเริ่มยาว 😆 ด้วยความที่ XLOOKUP ใช้งานได้หลายแบบ ถ้าในอ่านแล้วงงงง ก็ comment ไว้ได้เลยนะครับ

เอาไว้มาเขียนการใช้งานในแบบอื่นต่อในโพสต์ถัดไป

สิ่งที่ควรทราบ

• XLOOUP สามารถค้นหาได้ทั้งแบบแถว (Vertical) และคอลัมภ์ (Horizontal)
• หากไม่ระบุอาร์กิวเมนต์ if_not_found หาก XLOOKUP ไม่พบข้อมูล จะแสดงผลเป็น #N/A
• ⚠️ขนาดของ range หรือ array ของ lookup_array กับ return_array ต้องมีขนาดเท่ากัน ไม่เช่นนั้น XLOOPUP จะแสดงผลเป็น #VALUE!
• ⚠️หากใช้ XLOOKUP ไปดูข้อมูลจากตารางในไฟล์ (workbook) อื่น ไฟล์นั้น ๆ จะต้องเปิดไว้ด้วย ไม่เช่นนั้นคุณจะพบ #REF!

สรุป

XLOOPUP มีไว้ช่วยเราหาข้อมูลจากค่าคีย์เวิร์ด (keyword) หรือคำค้นที่เรารู้อยู่แล้ว จากตารางข้อมูลไม่ว่าจะเป็น range หรือ array