XLOOKUP ใช้ยังไง? อธิบาย Syntax ตัวอย่างการใช้งานจริง และเหตุผลที่ XLOOKUP ดีกว่า VLOOKUP ทุกกรณี พร้อม Tips ขั้นสูงที่ใช้ได้ทันที
ถ้าคุณยังใช้ VLOOKUP อยู่ บทความนี้จะเปลี่ยนวิธีทำงานของคุณไปตลอดกาล เพราะ XLOOKUP คือสูตร Excel ที่ทำทุกอย่างที่ VLOOKUP ทำได้ บวกอีกหลายสิบฟีเจอร์ที่ VLOOKUP ทำไม่ได้ และใช้งานง่ายกว่าด้วย
Microsoft เปิดตัว XLOOKUP ในปี 2019 เพื่อมาแทน VLOOKUP, HLOOKUP และบางส่วนของ INDEX MATCH แต่หลายคนยังไม่รู้ว่ามันทำอะไรได้บ้าง บทความนี้จะอธิบายตั้งแต่ Syntax พื้นฐานไปจนถึงการใช้ขั้นสูง พร้อมตัวอย่างที่เอาไปใช้ได้ทันที
XLOOKUP คืออะไร?
XLOOKUP คือฟังก์ชัน Lookup ใน Excel ที่ออกแบบมาให้ค้นหาข้อมูลในตารางหรือช่วงข้อมูล แล้วดึงค่าที่ต้องการกลับมา เหมือน VLOOKUP แต่ทรงพลังกว่าและยืดหยุ่นกว่ามาก
XLOOKUP ใช้ได้ใน: Microsoft 365 (Excel สมัครสมาชิก), Excel 2021, Excel 2019 บางเวอร์ชั่น และ Microsoft 365 บนเว็บ แต่ยังไม่รองรับใน Excel 2016 หรือเก่ากว่านั้น
Syntax ของ XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
อธิบายแต่ละส่วน:
lookup_value (จำเป็น): ค่าที่ต้องการค้นหา เช่น รหัสสินค้า, ชื่อพนักงาน, หรือ ID ลูกค้า
lookup_array (จำเป็น): คอลัมน์หรือแถวที่ต้องการค้นหาค่านั้น เช่น คอลัมน์รหัสสินค้าทั้งหมด
return_array (จำเป็น): คอลัมน์หรือแถวที่ต้องการดึงค่ากลับมา เช่น คอลัมน์ชื่อสินค้าหรือราคา
[if_not_found] (ไม่บังคับ): ข้อความที่จะแสดงถ้าหาไม่เจอ เช่น "ไม่พบข้อมูล" แทนที่จะแสดง #N/A
[match_mode] (ไม่บังคับ): วิธีจับคู่ค่า: 0 = ตรงทั้งหมด (ค่าเริ่มต้น), -1 = น้อยกว่าที่ใกล้ที่สุด, 1 = มากกว่าที่ใกล้ที่สุด, 2 = Wildcard Match
[search_mode] (ไม่บังคับ): ทิศทางการค้นหา: 1 = จากบนลงล่าง (ค่าเริ่มต้น), -1 = จากล่างขึ้นบน, 2 = Binary Search จากบน, -2 = Binary Search จากล่าง
สำหรับการใช้งานทั่วไป คุณต้องใส่แค่ 3 ส่วนแรก ส่วนที่เหลือไม่จำเป็นต้องใส่
ตัวอย่างพื้นฐาน: XLOOKUP แบบง่ายที่สุด
สมมติว่ามีตารางข้อมูลสินค้าดังนี้:
โจทย์: ต้องการรู้ราคาของรหัสสินค้า P003
สูตร XLOOKUP:
=XLOOKUP("P003", A2:A5, C2:C5)
ผลลัพธ์: 1,200
อธิบาย: ค้นหา "P003" ในคอลัมน์ A (รหัสสินค้า) แล้วดึงค่าจากคอลัมน์ C (ราคา) ในแถวเดียวกัน
ตัวอย่างขั้นกลาง: XLOOKUP กับ if_not_found
โจทย์: ค้นหาชื่อสินค้าของรหัส P999 (ที่ไม่มีอยู่ในตาราง)
สูตร VLOOKUP แบบเก่า (มีปัญหา):
=VLOOKUP("P999", A2:C5, 2, FALSE)
ผลลัพธ์: #N/A (ดูแย่มาก ต้องใช้ IFERROR ห่อเพิ่ม)
สูตร XLOOKUP แบบใหม่ (สะอาดกว่า):
=XLOOKUP("P999", A2:A5, B2:B5, "ไม่พบรหัสสินค้านี้")
ผลลัพธ์: ไม่พบรหัสสินค้านี้
XLOOKUP จัดการ Error ได้ในตัวเองโดยไม่ต้องใช้ IFERROR เพิ่ม ทำให้สูตรสั้นและอ่านง่ายขึ้น
ทำไม XLOOKUP ถึงดีกว่า VLOOKUP?
ข้อที่ 1: ค้นหาได้ทั้งซ้ายและขวา
VLOOKUP ค้นหาจากคอลัมน์แรกแล้วดึงค่าจากคอลัมน์ทางขวาเท่านั้น ถ้าอยากดึงค่าจากคอลัมน์ทางซ้ายจะทำไม่ได้
XLOOKUP ค้นหาได้ทุกทิศทาง ไม่ว่าค่าที่ต้องการจะอยู่ซ้ายหรือขวาของคอลัมน์ค้นหาก็ทำได้หมด
ตัวอย่าง: ถ้าคอลัมน์ราคาอยู่ทางซ้ายของคอลัมน์รหัสสินค้า XLOOKUP ยังทำงานได้ปกติ แต่ VLOOKUP ทำไม่ได้
ข้อที่ 2: ไม่ต้องนับหมายเลขคอลัมน์
VLOOKUP ต้องใส่ col_index_num เช่น 3 หมายความว่าดึงคอลัมน์ที่ 3 ปัญหาคือถ้าเพิ่มหรือลบคอลัมน์ในตาราง ตัวเลขนี้จะผิดทันทีทำให้ Formula พัง
XLOOKUP ระบุ return_array เป็นช่วง Cell โดยตรง เช่น C2:C5 ดังนั้นถึงจะเพิ่มหรือลบคอลัมน์ Formula ก็ยังทำงานถูกต้อง
ข้อที่ 3: จัดการ Error ในตัวเอง
VLOOKUP ต้องใช้ IFERROR ห่อทุกครั้งที่อยากแสดงข้อความแทน #N/A ทำให้ Formula ยาวและอ่านยาก XLOOKUP มี if_not_found Parameter ในตัวเลย
ข้อที่ 4: ค้นหาจากล่างขึ้นบนได้
VLOOKUP ค้นหาแค่จากบนลงล่าง ถ้ามีค่าซ้ำกันจะดึงค่าแถวบนสุดเสมอ XLOOKUP สามารถค้นหาจากล่างขึ้นบนได้ ซึ่งมีประโยชน์เมื่อต้องการค่าล่าสุดที่เพิ่มเข้าไปในตาราง
ตัวอย่าง: ตาราง Transaction ที่เรียงตามวันที่ ต้องการ Transaction ล่าสุดของลูกค้า:
=XLOOKUP("CUST001", A2:A1000, C2:C1000, "ไม่พบ", 0, -1)
search_mode = -1 หมายถึงค้นหาจากล่างขึ้นบน จะได้ Transaction ล่าสุดเสมอ
ข้อที่ 5: ดึงหลายคอลัมน์พร้อมกันได้
VLOOKUP ดึงได้ทีละคอลัมน์ ถ้าอยากดึง 3 คอลัมน์ต้องเขียน VLOOKUP 3 ครั้ง XLOOKUP ระบุ return_array เป็นหลายคอลัมน์พร้อมกันได้เลย
ตัวอย่าง: ดึงทั้งชื่อสินค้าและราคาพร้อมกัน: =XLOOKUP("P001", A2:A5, B2:C5)
จะแสดงผลทั้ง 2 คอลัมน์พร้อมกันใน 2 Cell ที่อยู่ติดกัน (ฟีเจอร์นี้ทำงานกับ Dynamic Arrays ใน Excel 365)
ตัวอย่างขั้นสูง: Wildcard Match ใน XLOOKUP
บางครั้งคุณไม่รู้ค่าที่ต้องการค้นหาแน่ชัด รู้แค่บางส่วน XLOOKUP รองรับ Wildcard ได้เมื่อตั้ง match_mode = 2
Wildcard ที่ใช้ได้:
* = แทนตัวอักษรจำนวนเท่าใดก็ได้
? = แทนตัวอักษรเพียง 1 ตัว
ตัวอย่าง: ค้นหาสินค้าที่ชื่อขึ้นต้นด้วย "รองเท้า":
=XLOOKUP("รองเท้า*", B2:B5, C2:C5, "ไม่พบ", 2)
จะดึงราคาของสินค้าแรกที่ชื่อขึ้นต้นด้วย "รองเท้า"
ตัวอย่างขั้นสูง: XLOOKUP ซ้อน XLOOKUP (Nested XLOOKUP)
ถ้าต้องการค้นหาแบบ 2 เงื่อนไขพร้อมกัน สามารถซ้อน XLOOKUP ได้
สถานการณ์: ตาราง Budget แยกตามแผนกและไตรมาส ต้องการดูงบของแผนก "IT" ในไตรมาส "Q2"
=XLOOKUP("IT", A2:A10, XLOOKUP("Q2", B1:E1, B2:E10))
สูตรด้านในค้นหาคอลัมน์ "Q2" ก่อน แล้วสูตรด้านนอกค้นหาแถว "IT" ในคอลัมน์นั้น ผลลัพธ์คือค่าที่ตัดกันของทั้ง 2 เงื่อนไข
XLOOKUP vs INDEX MATCH ใช้อันไหนดี?
ก่อน XLOOKUP มาถึง คนที่ต้องการความยืดหยุ่นกว่า VLOOKUP มักใช้ INDEX MATCH แทน แล้วสองอันนี้ต่างกันยังไง?
INDEX MATCH ยังคงมีข้อได้เปรียบในบางกรณี เช่น รองรับ Excel เวอร์ชั่นเก่า (2016 และก่อนหน้า), ประสิทธิภาพดีกว่าเล็กน้อยสำหรับไฟล์ขนาดใหญ่มากๆ และยืดหยุ่นกว่าในบางกรณีขั้นสูง
XLOOKUP เหมาะกว่าสำหรับ Excel 365 และ Excel 2021 เขียนสูตรสั้นกว่าและอ่านเข้าใจง่ายกว่า INDEX MATCH มาก และมีฟีเจอร์พิเศษที่ INDEX MATCH ทำไม่ได้ เช่น if_not_found และ search_mode ที่ยืดหยุ่น
สรุป: ถ้าใช้ Excel 365 หรือ 2021 ให้เปลี่ยนมาใช้ XLOOKUP เลย ถ้าต้องแชร์ไฟล์กับคนที่ใช้ Excel เวอร์ชั่นเก่า ให้ใช้ INDEX MATCH แทนเพื่อความเข้ากันได้
Tips การใช้ XLOOKUP ให้มีประสิทธิภาพสูงสุด
1.ใช้ Named Range แทนการระบุ Cell โดยตรง แทนที่จะเขียน A2:A100 ลอง Named Range เป็น "รหัสสินค้า" จะทำให้สูตรอ่านง่ายขึ้นมาก:
=XLOOKUP(F2, รหัสสินค้า, ชื่อสินค้า, "ไม่พบ")
2.Lock Reference ด้วย $ เมื่อจะ Copy Formula ถ้าจะ Copy XLOOKUP ไปหลาย Cell ให้ Lock lookup_array และ return_array ด้วย $:
=XLOOKUP(F2, $A$2:$A$100, $C$2:$C$100, "ไม่พบ")
3.ใช้ Table Reference แทน Range ถ้าข้อมูลอยู่ใน Excel Table (Insert > Table) สามารถอ้างอิงแบบนี้ได้:
=XLOOKUP(F2, Table1[รหัสสินค้า], Table1[ราคา], "ไม่พบ")
ข้อดีคือ Table จะขยายอัตโนมัติเมื่อเพิ่มข้อมูลใหม่ สูตรก็ทำงานถูกต้องโดยไม่ต้องแก้ไข
4. ใช้ XLOOKUP กับ Drop-down List สร้าง Drop-down List ใน Cell ที่ใช้เป็น lookup_value แล้วให้ XLOOKUP ดึงข้อมูลตามที่เลือก จะได้ Interactive Form แบบง่ายๆ ใน Excel
5. Combine กับ AI Tools ถ้าต้องการให้ AI ช่วยสร้างสูตร XLOOKUP ที่ซับซ้อน ลองอธิบายโจทย์ให้ ChatGPT หรือ Copilot ใน Excel แล้วให้มันเขียนสูตรให้ จะประหยัดเวลาได้มาก
ปัญหาที่พบบ่อยและวิธีแก้ไข
1.ปัญหา: ได้ผลลัพธ์ผิด ทั้งที่หาเจอ
สาเหตุมักเป็น Space พิเศษหรือตัวอักษรที่มองไม่เห็นในข้อมูล แก้โดยใช้ TRIM() ล้าง Space ก่อน:
=XLOOKUP(TRIM(F2), TRIM(A2:A100), C2:C100, "ไม่พบ")
2.ปัญหา: XLOOKUP ไม่ทำงาน แจ้ง Error ว่าไม่รู้จักฟังก์ชัน
หมายความว่า Excel ของคุณเวอร์ชั่นเก่าเกินไป ต้องอัปเกรดเป็น Microsoft 365 หรือ Excel 2021 จึงจะใช้ได้
3.ปัญหา: ต้องการค้นหาแบบ Case-sensitive (แยกพิมพ์ใหญ่-เล็ก)
XLOOKUP ไม่แยก Case โดยค่าเริ่มต้น ถ้าต้องการแยก ต้องใช้สูตรอาร์เรย์ขั้นสูงร่วมกับ EXACT()
ตารางสรุปเปรียบเทียบสูตร Lookup ทั้งหมด
XLOOKUP คือ evolution ของ VLOOKUP ที่ทำทุกอย่างได้ดีกว่า ปลอดภัยกว่า และเขียนสั้นกว่า ถ้าคุณใช้ Excel 365 หรือ Excel 2021 ไม่มีเหตุผลที่จะไม่เปลี่ยนมาใช้ XLOOKUP แทน VLOOKUP
เริ่มจากสูตรพื้นฐาน 3 ส่วนก่อน แล้วค่อยเพิ่ม if_not_found ทีหลัง แค่นั้นก็ครอบคลุมงาน 90% ที่คุณต้องใช้ในชีวิตประจำวันแล้ว สำหรับ Excel Tips, AI Tools สำหรับ Data Analysis และ Data Visualization เพิ่มเติม ติดตามได้ที่ Newslytix
คำถามที่พบบ่อยเกี่ยวกับ VLOOKUP
VLOOKUP กับ XLOOKUP อันไหนเรียนยากกว่ากัน?
ทั้งสองสูตรมีหลักการคล้ายกัน แต่ XLOOKUP เขียนสั้นและเข้าใจง่ายกว่า เพราะไม่ต้องนับหมายเลขคอลัมน์เหมือน VLOOKUP มือใหม่ที่ไม่เคยใช้สูตรใดมาก่อนจึงมักเรียน XLOOKUP ได้เร็วกว่า
XLOOKUP ใช้ได้กับ Excel เวอร์ชันไหนบ้าง?
XLOOKUP ใช้ได้เฉพาะ Excel 365 และ Excel 2021 ขึ้นไปเท่านั้น หากใช้ Excel เวอร์ชันเก่ากว่านี้หรือต้องแชร์ไฟล์กับคนที่ใช้เวอร์ชันเก่า จำเป็นต้องใช้ VLOOKUP แทน
ใช้ VLOOKUP อยู่แล้ว จำเป็นต้องเปลี่ยนไปใช้ XLOOKUP ไหม?
ไม่จำเป็นต้องเปลี่ยนทันที แต่แนะนำให้ฝึกใช้ XLOOKUP ควบคู่ไปด้วย เพราะยืดหยุ่นกว่าและลดความเสี่ยงจากข้อผิดพลาดเมื่อโครงสร้างตารางเปลี่ยนแปลง
