XLOOKUP คืออะไร? สอนใช้สูตร XLOOKUP แทน VLOOKUP พร้อมตัวอย่างงานจริง

ถ้าคุณเคยเขียน VLOOKUP แล้วเจอ #N/A ทั้งที่ข้อมูลมีอยู่จริง ต้องมานั่งนับว่าคอลัมน์ที่ต้องการเป็นคอลัมน์ที่เท่าไร หรือพอมีคนแทรกคอลัมน์ใหม่แล้วสูตรพังทั้งไฟล์ บทความนี้เหมาะกับคุณ XLOOKUP คือสูตรค้นหาข้อมูลรุ่นใหม่ของ Excel ที่แก้ปัญหาเหล่านี้ได้เกือบทั้งหมด และเขียนง่ายกว่าเดิม

เราจะอธิบายตั้งแต่ XLOOKUP คืออะไร โครงสร้างสูตร ไปจนถึงตัวอย่างที่ใช้ในงานจริง เช่น ดึงราคาสินค้า ค้นหาย้อนกลับไปทางซ้าย คิดค่าคอมมิชชันตามขั้นยอดขาย และค้นหาด้วย 2 เงื่อนไข พร้อมวิธีแก้ error ที่พบบ่อย

XLOOKUP คืออะไร

XLOOKUP เป็นฟังก์ชันค้นหาข้อมูล (Lookup) ที่ Microsoft เพิ่มเข้ามาแทน VLOOKUP และ HLOOKUP ทำหน้าที่ "หาค่าที่ต้องการในคอลัมน์หนึ่ง แล้วดึงข้อมูลในแถวเดียวกันจากอีกคอลัมน์หนึ่งมาแสดง" เช่น พิมพ์รหัสสินค้า แล้วให้ Excel ดึงชื่อสินค้าและราคามาให้อัตโนมัติ

จุดเด่นคือเราเลือก คอลัมน์ที่ใช้ค้นหา และ คอลัมน์ที่ต้องการผลลัพธ์ แยกกันได้อิสระ จึงไม่ต้องนับเลขคอลัมน์ ค้นหาไปทางซ้ายได้ และค่าเริ่มต้นเป็นการค้นหาแบบตรงตัว (Exact match) ซึ่งเป็นสิ่งที่คนส่วนใหญ่ต้องการอยู่แล้ว

Excel เวอร์ชันไหนใช้ XLOOKUP ได้

• Microsoft 365 (Excel สำหรับ Windows และ Mac)
• Excel 2021 และ Excel 2024
• Excel สำหรับเว็บ (Excel Online) และแอป Excel บนมือถือ
• Google Sheets ก็มีฟังก์ชัน XLOOKUP เช่นกัน

ส่วน Excel 2019, 2016 และเก่ากว่า ไม่มี XLOOKUP ถ้าเปิดไฟล์ที่ใช้สูตรนี้ในเวอร์ชันเก่าจะขึ้น #NAME? ถ้าต้องส่งไฟล์ให้คนที่ใช้ Excel รุ่นเก่า ให้ใช้ INDEX + MATCH แทน (มีตัวอย่างท้ายบทความ)

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

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

อาร์กิวเมนต์	ความหมาย	จำเป็นไหม
lookup_value	ค่าที่ต้องการค้นหา เช่น รหัสสินค้าในเซลล์ F2	จำเป็น
lookup_array	คอลัมน์ (หรือแถว) ที่ใช้ค้นหาค่านั้น	จำเป็น
return_array	คอลัมน์ที่ต้องการดึงผลลัพธ์กลับมา	จำเป็น
if_not_found	ข้อความหรือค่าที่จะแสดงเมื่อหาไม่เจอ แทน #N/A	ไม่จำเป็น
match_mode	0 = ตรงตัว (ค่าเริ่มต้น), -1 = ตรงตัวหรือค่าที่เล็กกว่าถัดไป, 1 = ตรงตัวหรือค่าที่ใหญ่กว่าถัดไป, 2 = ใช้ Wildcard (* ?)	ไม่จำเป็น
search_mode	1 = ค้นจากบนลงล่าง (ค่าเริ่มต้น), -1 = ค้นจากล่างขึ้นบน, 2 / -2 = Binary search สำหรับข้อมูลที่เรียงแล้ว	ไม่จำเป็น

จำง่าย ๆ ว่าใช้แค่ 3 ช่องแรกก็พอสำหรับงานส่วนใหญ่: หาอะไร, หาที่ไหน, เอาอะไรกลับมา

ข้อมูลตัวอย่างที่ใช้ในบทความนี้

สมมติเรามีตารางสินค้าอยู่ในช่วง A1:D7 ดังนี้

	A: รหัสสินค้า	B: ชื่อสินค้า	C: ราคา (บาท)	D: คงเหลือ
2	P001	เมาส์ไร้สาย	390	120
3	P002	คีย์บอร์ด	890	45
4	P003	จอมอนิเตอร์ 24 นิ้ว	4,290	12
5	P004	หูฟัง	1,190	60
6	P005	เว็บแคม	1,490	8
7	P006	แฟลชไดรฟ์ 64GB	259	200

และพิมพ์รหัสสินค้าที่ต้องการค้นหาไว้ที่เซลล์ F2

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

1. ดึงราคาสินค้าจากรหัส (การใช้งานพื้นฐาน)

=XLOOKUP(F2, A2:A7, C2:C7)

ถ้า F2 คือ P003 ผลลัพธ์คือ 4,290 สังเกตว่าไม่ต้องใส่เลขคอลัมน์ และไม่ต้องใส่ FALSE ท้ายสูตรเหมือน VLOOKUP เพราะ XLOOKUP ค้นหาแบบตรงตัวเป็นค่าเริ่มต้น

เคล็ดลับ: ถ้าจะลากสูตรลงหลายแถว ให้ล็อกช่วงข้อมูลด้วย $ เช่น =XLOOKUP(F2, $A$2:$A$7, $C$2:$C$7) หรือแปลงข้อมูลเป็น Table (Ctrl + T) แล้วอ้างอิงชื่อคอลัมน์แทน จะไม่ต้องแก้ช่วงเมื่อมีข้อมูลเพิ่ม

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

=XLOOKUP(F2, A2:A7, C2:C7, "ไม่พบรหัสสินค้า")

เดิมเราต้องครอบด้วย IFERROR หรือ IFNA แต่ XLOOKUP มีช่อง if_not_found ให้ในตัว ข้อดีคือจะแทนเฉพาะกรณี "หาไม่เจอ" เท่านั้น ไม่ไปกลบ error อื่นที่ควรรู้ เช่น สูตรอ้างอิงผิด

3. ค้นหาย้อนไปทางซ้าย (สิ่งที่ VLOOKUP ทำไม่ได้)

=XLOOKUP("หูฟัง", B2:B7, A2:A7)

ค้นหาจากชื่อสินค้าในคอลัมน์ B แล้วดึงรหัสจากคอลัมน์ A ซึ่งอยู่ทางซ้าย ได้ผลลัพธ์ P004 ถ้าเป็น VLOOKUP ต้องย้ายคอลัมน์ หรือเปลี่ยนไปใช้ INDEX + MATCH

4. ดึงหลายคอลัมน์พร้อมกันในสูตรเดียว

=XLOOKUP(F2, A2:A7, B2:D7)

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

5. คิดค่าคอมมิชชันตามขั้นยอดขาย (Approximate match)

สมมติมีตารางอัตราค่าคอมมิชชันในช่วง H2:I5

H: ยอดขายขั้นต่ำ	I: อัตราคอมมิชชัน
0	0%
50,000	3%
100,000	5%
200,000	7%

=XLOOKUP(E2, H2:H5, I2:I5, , -1)

match_mode = -1 หมายถึง "ถ้าไม่เจอค่าที่ตรงกัน ให้ใช้ค่าที่น้อยกว่าถัดไป" ถ้ายอดขายใน E2 คือ 135,000 จะได้อัตรา 5% และที่ดีกว่า VLOOKUP แบบ TRUE คือ ไม่ต้องเรียงข้อมูลจากน้อยไปมาก ก็ยังได้ผลถูกต้อง ใช้กับตารางภาษี เกรด หรือส่วนลดตามขั้นได้เหมือนกัน

6. หาข้อมูลรายการล่าสุด (ค้นจากล่างขึ้นบน)

ถ้ามีประวัติการสั่งซื้อที่ลูกค้าคนเดียวซื้อหลายครั้ง แล้วอยากรู้วันที่ซื้อ ครั้งล่าสุด

=XLOOKUP(G2, A2:A500, C2:C500, "ยังไม่เคยซื้อ", 0, -1)

search_mode = -1 สั่งให้ค้นจากแถวล่างสุดขึ้นไป จึงได้รายการล่าสุด (เมื่อข้อมูลเรียงตามวันที่จากเก่าไปใหม่) ซึ่ง VLOOKUP จะคืนค่าแถวแรกที่เจอเสมอ

7. ค้นหาด้วย 2 เงื่อนไข

เช่น ต้องการยอดขายของ พนักงาน ใน G2 เฉพาะ เดือน ใน H2 จากตารางที่คอลัมน์ A เป็นชื่อพนักงาน คอลัมน์ B เป็นเดือน และคอลัมน์ C เป็นยอดขาย

=XLOOKUP(1, (A2:A500=G2)*(B2:B500=H2), C2:C500, "ไม่พบข้อมูล")

ส่วน (A2:A500=G2)*(B2:B500=H2) จะได้ค่า 1 เฉพาะแถวที่ตรงทั้งสองเงื่อนไข แล้ว XLOOKUP ก็หาเลข 1 ตัวแรกให้ ใช้ได้กับ Microsoft 365 และ Excel 2021 ขึ้นไปโดยไม่ต้องกด Ctrl + Shift + Enter

XLOOKUP กับ VLOOKUP ต่างกันอย่างไร

หัวข้อ	VLOOKUP	XLOOKUP
ค่าเริ่มต้นการค้นหา	แบบประมาณ (ต้องใส่ FALSE เอง)	แบบตรงตัว
ค้นหาไปทางซ้าย	ไม่ได้	ได้
ต้องระบุเลขคอลัมน์	ต้อง (แทรกคอลัมน์แล้วสูตรเพี้ยน)	ไม่ต้อง เลือกคอลัมน์ผลลัพธ์โดยตรง
ข้อความเมื่อหาไม่เจอ	ต้องครอบ IFERROR / IFNA	มีช่อง if_not_found ในตัว
ค้นจากล่างขึ้นบน	ไม่ได้	ได้ (search_mode -1)
ดึงหลายคอลัมน์	ต้องเขียนหลายสูตร	สูตรเดียว (Spill)
ค้นหาแนวนอน	ต้องใช้ HLOOKUP	ใช้ XLOOKUP ตัวเดียวได้ทั้งแนวตั้งและแนวนอน
รองรับ Excel รุ่นเก่า	ทุกเวอร์ชัน	Microsoft 365 / 2021 ขึ้นไป

สรุปคือ ถ้าทุกคนในทีมใช้ Microsoft 365 หรือ Excel 2021 ขึ้นไป แนะนำให้เปลี่ยนมาใช้ XLOOKUP ได้เลย แต่ถ้าต้องส่งไฟล์ให้ลูกค้าหรือหน่วยงานที่ใช้ Excel รุ่นเก่า VLOOKUP หรือ INDEX + MATCH ยังจำเป็นอยู่

Error ที่พบบ่อยและวิธีแก้

#N/A หาไม่เจอ ทั้งที่ข้อมูลมีอยู่

• มีช่องว่างเกิน เช่น "P001 " ที่มีเว้นวรรคท้าย แก้โดยใช้ TRIM ล้างข้อมูล หรือเขียน =XLOOKUP(TRIM(F2), A2:A7, C2:C7)
• ตัวเลขที่เก็บเป็นข้อความ เช่น รหัส 1001 ฝั่งหนึ่งเป็นตัวเลข อีกฝั่งเป็นข้อความ (มักเห็นสามเหลี่ยมสีเขียวมุมเซลล์) แก้โดยแปลงให้เป็นชนิดเดียวกัน หรือใช้ F2&"" / VALUE(F2) ในสูตร
• อักขระพิเศษที่มองไม่เห็น จากการคัดลอกข้อมูลจากระบบอื่น ใช้ CLEAN ช่วยล้าง

#VALUE!

มักเกิดจาก lookup_array และ return_array มีจำนวนแถวไม่เท่ากัน เช่น A2:A7 กับ C2:C8 ให้ตรวจว่าช่วงเริ่มและจบที่แถวเดียวกัน

#SPILL!

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

#NAME?

แปลว่า Excel เวอร์ชันนั้นไม่รู้จัก XLOOKUP (Excel 2019 หรือเก่ากว่า) หรือพิมพ์ชื่อฟังก์ชันผิด

ทางเลือกสำหรับ Excel รุ่นเก่า: INDEX + MATCH

ถ้าใช้ XLOOKUP ไม่ได้ สูตรนี้ให้ผลเหมือนตัวอย่างที่ 1 และค้นหาไปทางซ้ายได้เช่นกัน

=INDEX(C2:C7, MATCH(F2, A2:A7, 0))

MATCH หาว่า F2 อยู่แถวที่เท่าไรในคอลัมน์ A แล้ว INDEX ดึงค่าจากแถวนั้นในคอลัมน์ C (เลข 0 ท้าย MATCH คือการค้นหาแบบตรงตัว)

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

XLOOKUP ช้ากว่า VLOOKUP ไหม

สำหรับไฟล์ทั่วไปแทบไม่ต่างกัน ถ้าข้อมูลหลักแสนแถวขึ้นไปและเรียงลำดับไว้แล้ว สามารถตั้ง search_mode เป็น 2 (Binary search) เพื่อให้ค้นหาเร็วขึ้นได้

XLOOKUP แยกตัวพิมพ์เล็ก-ใหญ่ไหม

ไม่แยก "p001" กับ "P001" ถือว่าเป็นค่าเดียวกัน

ใช้ XLOOKUP ค้นหาข้ามชีตหรือข้ามไฟล์ได้ไหม

ได้ เช่น =XLOOKUP(F2, สินค้า!A2:A7, สินค้า!C2:C7) แค่ให้ lookup_array และ return_array อยู่ในชีตเดียวกันและมีขนาดเท่ากัน

ค้นหาด้วยคำบางส่วนได้ไหม

ได้ ตั้ง match_mode เป็น 2 แล้วใช้ Wildcard เช่น =XLOOKUP("*จอ*", B2:B7, C2:C7, "ไม่พบ", 2) จะหาสินค้าที่มีคำว่า "จอ" อยู่ในชื่อ

สรุป

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

ถ้าอยากฝึกสูตรค้นหาอย่าง VLOOKUP, XLOOKUP, INDEX + MATCH ร่วมกับ SUMIFS, COUNTIFS และ Pivot Table จากข้อมูลตัวอย่างจริงพร้อมวิทยากรดูแลใกล้ชิด ดูรายละเอียดได้ที่ หลักสูตร Excel Advance หรือถ้ายังไม่คล่องพื้นฐาน เริ่มที่ หลักสูตร Excel Basic ก่อนได้ สำหรับองค์กรที่ต้องการจัดอบรมให้ทีมโดยใช้ข้อมูลของบริษัทเอง ดูรายละเอียดที่ อบรม In-house

อ่านเทคนิค Excel อื่น ๆ เพิ่มเติมได้ที่ บทความทั้งหมด