XLOOKUP Excel ใช้อย่างไร? พร้อมตัวอย่างแทน VLOOKUP สำหรับงานจริง

START HERE

XLOOKUP คืออะไร? ทำไมคนใช้ Excel ควรรู้จัก

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

จุดที่ทำให้ XLOOKUP ใช้ง่ายกว่า VLOOKUP ในหลายสถานการณ์คือ เราระบุ ช่วงที่ค้นหา และ ช่วงที่ต้องการคืนค่า แยกกัน จึงไม่ต้องนับว่า “ค่าที่ต้องการอยู่คอลัมน์ที่เท่าไรของตาราง” และยังคืนค่าจากคอลัมน์ด้านซ้ายของคอลัมน์ค้นหาได้ด้วย

XLOOKUP เหมาะกับงานแบบไหน?

FORMULA STRUCTURE

โครงสร้างสูตร XLOOKUP

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

BASIC EXAMPLE

ตัวอย่างพื้นฐาน: ค้นหารหัสสินค้าแล้วดึงชื่อสินค้า

สมมติว่าเรามี Master สินค้าอยู่ในคอลัมน์ H:K และต้องการกรอกรหัสสินค้าที่ B2 เพื่อให้ Excel ดึงชื่อสินค้ากลับมาอัตโนมัติ

ตัวอย่างนี้กำหนดให้ B3 เป็นค่าที่ค้นหา และ H3:H7 เป็นช่วงรหัสสินค้า

=XLOOKUP(B3,$H$3:$H$7,$I$3:$I$7)

สูตรนี้อ่านได้ตรง ๆ ว่า “นำค่าจาก B3 ไปค้นหาใน H3:H7 ถ้าเจอให้คืนค่าจาก I3:I7 ในแถวเดียวกัน” ผลลัพธ์คือ Keyboard

ถ้าต้องการดึงราคาแทนชื่อสินค้า

=XLOOKUP(B3,$H$3:$H$7,$K$3:$K$7)

เปลี่ยนเฉพาะ return_array จากคอลัมน์ชื่อสินค้า I3:I7 เป็นคอลัมน์ราคา K3:K7 ก็จะได้ผลลัพธ์ 1,290

IF NOT FOUND

ถ้าค้นหาไม่เจอ ให้แสดงข้อความแทน #N/A

ถ้ารหัสที่ค้นหาไม่มีอยู่ใน Master Data XLOOKUP จะคืนค่า #N/A เมื่อเราไม่ได้กำหนดค่าพิเศษไว้ แต่สามารถใช้ Argument ตัวที่ 4 เพื่อบอก Excel ว่าต้องการให้แสดงอะไรแทนได้เลย

=XLOOKUP(B3,$H$3:$H$7,$I$3:$I$7,"ไม่พบรหัสสินค้า")

LOOK LEFT OR RIGHT

XLOOKUP ค้นหาย้อนกลับจากขวาไปซ้ายได้

จุดที่หลายคนชอบใน XLOOKUP คือ คอลัมน์ที่ใช้ค้นหาไม่จำเป็นต้องอยู่ซ้ายสุดของตาราง เพราะเราเลือก lookup_array และ return_array แยกกัน

ตัวอย่างเช่น คอลัมน์ H เป็นชื่อแผนก แต่คอลัมน์ I เป็นรหัสพนักงาน เราสามารถค้นหารหัสจากคอลัมน์ I แล้วคืนชื่อแผนกจากคอลัมน์ H ได้โดยตรง

=XLOOKUP(B2,$I$3:$I$7,$H$3:$H$7,"ไม่พบพนักงาน")

VLOOKUP แบบปกติค้นหาโดยอาศัยคอลัมน์แรกของ table_array ดังนั้นการค้นหาจากขวาไปซ้ายมักต้องจัดโครงสร้างข้อมูลใหม่ หรือใช้วิธีอื่น เช่น INDEX + MATCH แต่ XLOOKUP ทำได้ตรงกว่าในสถานการณ์นี้

XLOOKUP VS VLOOKUP

XLOOKUP ต่างจาก VLOOKUP อย่างไร?

สูตรเดียวกันในมุม XLOOKUP และ VLOOKUP

=XLOOKUP(B3,$H$3:$H$7,$I$3:$I$7,"ไม่พบ")

=IFNA(VLOOKUP(B3,$H$3:$K$7,2,FALSE),"ไม่พบ")

ไม่ได้หมายความว่า VLOOKUP “ใช้ไม่ได้” ครับ ถ้าไฟล์ขององค์กรยังต้องรองรับ Excel 2016 หรือ 2019 VLOOKUP, INDEX/MATCH หรือสูตรอื่นอาจยังจำเป็น สิ่งสำคัญคือเลือกสูตรให้เหมาะกับเวอร์ชันและคนที่ต้องใช้ไฟล์ร่วมกัน

USEFUL TECHNIQUES

3 เทคนิค XLOOKUP ที่ช่วยงานจริงได้มากขึ้น

1) คืนหลายคอลัมน์จากสูตรเดียว

ถ้าต้องการดึงทั้งชื่อสินค้า ประเภท และราคา ไม่จำเป็นต้องเขียน XLOOKUP แยก 3 สูตร เราสามารถให้ return_array ครอบคลุมหลายคอลัมน์ได้

=XLOOKUP(B3,$H$3:$H$7,$I$3:$K$7,"ไม่พบรหัสสินค้า")

ผลลัพธ์จะกระจายออกไปยังเซลล์ข้างเคียง ดังนั้นพื้นที่ปลายทางต้องว่าง หากมีข้อมูลขวางอยู่ Excel อาจแสดง #SPILL!

2) ค้นหาข้อความบางส่วนด้วย Wildcard

กรณีต้องการค้นหาชื่อที่ “มีคำนี้อยู่ข้างใน” สามารถใช้ Wildcard ร่วมกับ match_mode = 2

=XLOOKUP("*"&B3&"*",$I$3:$I$7,$K$3:$K$7,"ไม่พบ",2)

ถ้าใน I3:I7 มีคำว่า Wireless Mouse สูตรจะสามารถจับคู่ด้วย Wildcard แล้วคืนราคาจาก K3:K7 ได้

3) ต้องการรายการล่าสุด? ค้นจากล่างขึ้นบน

ถ้ารหัสเดียวกันเกิดซ้ำหลายครั้ง เช่น ลูกค้าคนเดิมมีหลายรายการซื้อ เราสามารถใช้ search_mode = -1 เพื่อให้ XLOOKUP เริ่มค้นจากรายการสุดท้ายย้อนขึ้นมา

=XLOOKUP(B2,$H$3:$H$20,$J$3:$J$20,"ไม่พบ",0,-1)

WORKSHOP

Workshop: ดึงข้อมูลสินค้าเพื่อทำใบสั่งซื้อ

สมมติฝ่ายจัดซื้อได้รับรายการรหัสสินค้า แล้วต้องเติมชื่อสินค้า หมวดสินค้า และราคามาตรฐาน ก่อนนำไปตรวจสอบและส่งต่อ

Master Data สมมติว่าอยู่ H3:K7 โดย H = Product ID, I = Product Name, J = Category, K = Price

1. กรอกรหัสสินค้าใน A2

   เช่น P002 และ Copy รหัสรายการอื่นลงแถวถัดไป

2. ดึงชื่อสินค้าใน B2

   ใช้ XLOOKUP ค้นหา A2 ใน Master Product ID แล้วคืนชื่อสินค้า

3. ดึง Category และ Price

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

4. Copy สูตรลงรายการทั้งหมด

   ตรวจสอบรหัสที่ขึ้น “ไม่พบรหัสสินค้า” ก่อนนำข้อมูลไปทำรายงานหรือสั่งซื้อ

=XLOOKUP(A2,$H$3:$H$7,$I$3:$I$7,"ไม่พบรหัสสินค้า")

=XLOOKUP(A2,$H$3:$H$7,$J$3:$J$7,"ไม่พบรหัสสินค้า")

=XLOOKUP(A2,$H$3:$H$7,$K$3:$K$7,"ไม่พบรหัสสินค้า")

COMMON MISTAKES

ข้อผิดพลาด XLOOKUP ที่พบบ่อย

FAQ & SUMMARY

คำถามที่พบบ่อยเกี่ยวกับ XLOOKUP

สรุปจำง่าย

อ้างอิงคุณสมบัติและ Syntax: Microsoft Support — XLOOKUP และ VLOOKUP