Thep Excel
XMATCH – ฟังก์ชันค้นหาตำแหน่งข้อมูลขั้นสูง
XMATCH คืนค่าตำแหน่งของข้อมูลที่ค้นหาในช่วงหรืออาร์เรย์ ถือเป็นฟังก์ชันรุ่นใหม่ที่ Microsoft พัฒนามาแก้จุดอ่อนของ MATCH โดยเฉพาะเรื่องค่า Default ที่เป็น Exact Match แทน Approximate Match ทำให้ใช้ง่ายและปลอดภัยกว่ามาก นอกจากนี้ยังรองรับการค้นหาย้อนกลับ Binary Search สำหรับข้อมูลขนาดใหญ่ และการค้นหาแบบ Wildcard ทำให้ยืดหยุ่นกว่า MATCH เดิมหลายเท่า
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Function Metrics
Popularity
8/10
Difficulty
5/10
Usefulness
9/10
Syntax & Arguments
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| lookup_value | any | Yes | ค่าที่ต้องการค้นหา สามารถเป็นตัวเลข ข้อความ วันที่ หรือค่าตรรกะ | |
| lookup_array | range | Yes | ช่วงหรืออาร์เรย์ที่ต้องการค้นหา ต้องเป็นช่วงแถวหรือคอลัมน์เดียว | |
| match_mode | number | Optional | 0 | โหมดการจับคู่: 0 = ตรงทั้งหมด (ค่าเริ่มต้น) -1 = ตรงทั้งหมด หรือค่าที่เล็กกว่าที่ใกล้เคียงที่สุด 1 = ตรงทั้งหมด หรือค่าที่ใหญ่กว่าที่ใกล้เคียงที่สุด 2 = ค้นหาแบบ Wildcard โดย * ? และ ~ มีความหมายพิเศษ |
| search_mode | number | Optional | 1 | โหมดการค้นหา: 1 = ค้นหาจากต้นไปท้าย (ค่าเริ่มต้น) -1 = ค้นหาจากท้ายไปต้น (ย้อนกลับ) 2 = ค้นหาแบบไบนารีจากน้อยไปมาก (ต้องเรียงข้อมูลจากน้อยไปมาก) -2 = ค้นหาแบบไบนารีจากมากไปน้อย (ต้องเรียงข้อมูลจากมากไปน้อย) |
How it works
ค้นหาตำแหน่งสินค้าในรายการ
ใช้ XMATCH ร่วมกับ INDEX เพื่อค้นหาข้อมูลสินค้าจากรหัสหรือชื่อสินค้า แทน VLOOKUP ที่มีข้อจำกัด
หาข้อมูลล่าสุดในรายการซ้ำ
ใช้ search_mode = -1 เพื่อค้นหาย้อนกลับและหาตำแหน่งสุดท้ายของข้อมูลที่ซ้ำกัน เช่น ธุรกรรมล่าสุดของลูกค้า
ค้นหาเร็วในข้อมูลขนาดใหญ่
ใช้ search_mode = 2 หรือ -2 เพื่อค้นหาแบบไบนารี ซึ่งเร็วกว่าการค้นหาปกติมาก เหมาะสำหรับข้อมูลหลักพันหรือหลักหมื่นแถว
ค้นหาข้อความบางส่วน
ใช้ match_mode = 2 พร้อม Wildcard (* และ ?) เพื่อค้นหารหัสสินค้าหรือชื่อที่ตรงกับรูปแบบบางส่วน
Context notes
ต้องใช้ Excel for Microsoft 365, Excel 2024, Excel 2021 หรือ Excel for the web ไม่รองรับใน Excel 2019 หรือเวอร์ชันเก่ากว่า
Performance & limits
สำหรับข้อมูลขนาดใหญ่ (มากกว่า 1,000 แถว) ที่เรียงลำดับแล้ว แนะนำให้ใช้ Binary Search (search_mode = 2 หรือ -2) เพื่อเพิ่มความเร็วในการค้นหาอย่างมาก
Examples
ตัวอย่างที่ 1: ค้นหาตำแหน่งพนักงานจากรหัส
XMATCH("EMP005", EmployeeIDs)
.
สูตรค้นหารหัส "EMP005" ในช่วง EmployeeIDs และคืนค่าตำแหน่งที่ 5 (หาก "EMP005" อยู่ลำดับที่ 5 ในช่วง)
.
ที่เจ๋งคือ XMATCH จะทำ Exact Match ให้อัตโนมัติ ไม่ต้องมานั่งกังวลเหมือน MATCH ที่ Default เป็น Approximate Match แล้วได้ผลลัพธ์ผิดๆ 😅
=XMATCH("EMP005", EmployeeIDs)
5
ตัวอย่างที่ 2: ใช้ร่วมกับ INDEX เพื่อค้นหาชื่อพนักงาน
INDEX(EmployeeNames, XMATCH("EMP005", EmployeeIDs))
– EmployeeNames: ช่วงเซลล์ที่มีรายชื่อพนักงาน
– EmployeeIDs: ช่วงเซลล์ที่มีรหัสพนักงาน
.
สูตรทำงาน 2 ขั้นตอน:
1. XMATCH ค้นหารหัส "EMP005" ในช่วง EmployeeIDs และคืนค่าตำแหน่ง
2. INDEX ดึงค่าจากช่วง EmployeeNames ตามตำแหน่งที่ XMATCH คืนค่ามา
.
คอมโบนี้แทน VLOOKUP ได้เลยครับ แถมยังยืดหยุ่นกว่าอีก เพราะค้นหาได้ทุกทิศทาง ไม่จำกัดว่าข้อมูลต้องอยู่ทางซ้ายหรือขวา 😎
=INDEX(EmployeeNames, XMATCH("EMP005", EmployeeIDs))
สมชาย ใจดี
ตัวอย่างที่ 3: ค้นหาย้อนกลับเพื่อหาธุรกรรมล่าสุด
INDEX(TransactionAmounts, XMATCH("CUST001", CustomerIDs, 0, -1))
– TransactionAmounts: ช่วงเซลล์ยอดซื้อ
– CustomerIDs: ช่วงเซลล์รหัสลูกค้า (มีรหัสซ้ำได้)
.
สูตรค้นหา "CUST001" โดยใช้ search_mode = -1 (ค้นหาจากล่างขึ้นบน) เพื่อหาธุรกรรมล่าสุด:
1. XMATCH ค้นหาจากล่างขึ้นบน หาตำแหน่งสุดท้ายที่มีรหัส "CUST001"
2. INDEX ดึงยอดซื้อจาก TransactionAmounts ของธุรกรรมนั้น
.
ฟีเจอร์นี้เจ๋งมากครับ MATCH เดิมทำไม่ได้แบบนี้ ต้องมาใช้ Array Formula ซับซ้อนอลหม่าน 😅 แต่ XMATCH ทำให้ง่ายขึ้นเยอะเลย
=INDEX(TransactionAmounts, XMATCH("CUST001", CustomerIDs, 0, -1))
15000
ตัวอย่างที่ 4: ค้นหาแบบ Wildcard
XMATCH("PD*", ProductCodes, 2)
.
สูตรใช้ match_mode = 2 เพื่อค้นหาแบบ Wildcard:
– "PD*" หมายถึงรหัสที่ขึ้นต้นด้วย "PD" ตามด้วยอักขระใดก็ได้
– สูตรคืนค่า 3 คือตำแหน่งแรกที่พบรหัสขึ้นต้นด้วย PD
.
ใช้ Wildcard pattern ได้เหมือน COUNTIF หรือ SUMIF เลยครับ * = อะไรก็ได้กี่ตัวก็ได้, ? = ตัวอักษร 1 ตัว 💡
=XMATCH("PD*", ProductCodes, 2)
3
ตัวอย่างที่ 5: ค้นหาเร็วด้วย Binary Search
XMATCH(25000, SalesDataSorted, 0, 2)
.
สูตรใช้ search_mode = 2 (Binary Search แบบน้อยไปมาก):
– ค้นหายอดขาย 25,000 ใน SalesDataSorted ที่เรียงลำดับแล้ว
– Binary Search เร็วมากกกก เหมาะสำหรับข้อมูลหลักพัน-หลักหมื่นแถว
.
⚠️ **ข้อควรระวัง:** ต้องแน่ใจว่าข้อมูลเรียงลำดับอย่างถูกต้อง มิฉะนั้นจะได้ผลลัพธ์ผิดโดยไม่มี error ขึ้นเตือน!
=XMATCH(25000, SalesDataSorted, 0, 2)
8245
FAQs
XMATCH ต่างจาก MATCH อย่างไร?
XMATCH เป็นฟังก์ชันรุ่นใหม่ที่ Microsoft สร้างมาแก้จุดอ่อนของ MATCH เลยครับ
.
ข้อดีที่เด่นชัด:
1️⃣ ค่า Default เป็น Exact Match (MATCH เป็น Approximate Match ซึ่งทำให้เจอ bug บ่อยมาก 😅)
2️⃣ ค้นหาย้อนกลับได้ (search_mode = -1) เหมาะหาข้อมูลล่าสุด
3️⃣ รองรับ Binary Search เร็วมากสำหรับข้อมูลใหญ่
4️⃣ ค้นหาแบบ Wildcard ได้ (* และ ?)
5️⃣ ไม่ต้องเรียงข้อมูลสำหรับ Approximate Match
.
โดยรวมแล้วใช้ง่ายกว่าและปลอดภัยกว่า MATCH หลายเท่าเลยครับ 😎
เมื่อไหร่ควรใช้ XMATCH แทน VLOOKUP?
ส่วนตัวผมแนะนำให้ใช้ INDEX+XMATCH แทน VLOOKUP เลยครับ ในกรณีเหล่านี้:
.
✅ **ควรใช้ INDEX+XMATCH เมื่อ:**
1️⃣ ต้องการค้นหาจากคอลัมน์ที่ไม่ใช่คอลัมน์ซ้ายสุด (VLOOKUP ทำไม่ได้)
2️⃣ ต้องการป้องกันสูตรพังเมื่อมีการเพิ่ม/ลบคอลัมน์
3️⃣ ต้องการค้นหาย้อนกลับหรือใช้ Binary Search
4️⃣ ต้องการความยืดหยุ่นในการระบุช่วงข้อมูลอย่างอิสระ
.
จริงๆ แล้ว VLOOKUP เหมาะสำหรับงานค้นหาง่ายๆ ที่ไม่ซับซ้อนเท่านั้น ถ้าทำงานกับข้อมูลใหญ่หรือซับซ้อน INDEX+XMATCH จะดีกว่าเยอะครับ 💡
ต้องมี Excel เวอร์ชันไหนถึงจะใช้ XMATCH ได้?
XMATCH ใช้ได้เฉพาะ:
– Excel for Microsoft 365
– Excel 2024
– Excel 2021
– Excel for the web
.
❌ **ใช้ไม่ได้ใน:** Excel 2019 หรือเวอร์ชันเก่ากว่า
.
ถ้าใช้ Excel เวอร์ชันเก่าอยู่ ก็ต้องใช้ MATCH แบบดั้งเดิมแทนครับ แต่ถ้ามีโอกาสอัปเกรด แนะนำให้ลองใช้ Excel 365 นะครับ ได้ XMATCH, XLOOKUP และฟังก์ชันใหม่ๆ อีกเพียบ 😎
Binary Search ใน XMATCH เร็วกว่าการค้นหาปกติมากแค่ไหน?
เร็วกว่า**มากกกก**ครับ ยิ่งข้อมูลเยอะยิ่งได้เปรียบ 🚀
.
**เปรียบเทียบความเร็ว:**
– ข้อมูล 1,000 แถว → เร็วกว่า ~10 เท่า
– ข้อมูล 10,000 แถว → เร็วกว่า ~100 เท่า
– ข้อมูล 100,000 แถว → เร็วกว่าหลายร้อยเท่า!
.
⚠️ **ข้อแม้สำคัญ:** ข้อมูล**ต้องเรียงลำดับถูกต้อง**ก่อนใช้
– search_mode = 2 → ต้องเรียงน้อยไปมาก
– search_mode = -2 → ต้องเรียงมากไปน้อย
.
ถ้าข้อมูลไม่ได้เรียง จะได้ผลลัพธ์ผิดโดยไม่มี error เตือน ระวังตรงนี้มากครับ! 😅
XMATCH คืนค่า #N/A เมื่อไหร่?
XMATCH จะคืนค่า #N/A ในกรณีเหล่านี้:
.
❌ **สาเหตุที่เจอบ่อย:**
1️⃣ ไม่พบข้อมูลที่ค้นหา (กรณี match_mode = 0)
2️⃣ ใช้ Binary Search กับข้อมูลที่ไม่ได้เรียงลำดับ
3️⃣ lookup_array ไม่ใช่ช่วงแถวหรือคอลัมน์เดียว (เป็นช่วง 2 มิติ)
4️⃣ ค่า match_mode หรือ search_mode ไม่ถูกต้อง
.
💡 **วิธีแก้:** ใช้ IFERROR หรือ IFNA ครอบไว้
“`
=IFERROR(XMATCH(…), “ไม่พบข้อมูล”)
“`
.
แบบนี้จะได้ข้อความแจ้งเตือนที่อ่านง่ายกว่า #N/A ครับ 😊
Resources & Related
Resources
-
Microsoft Support: XMATCH function
documentation -
Exceljet: XMATCH function
tutorial -
XLOOKUP vs INDEX and MATCH – Exceljet
tutorial -
ExcelJet
guide
Related posts
Related functions
Additional Notes
XMATCH คือฟังก์ชันค้นหาตำแหน่งที่ Microsoft สร้างมาเพื่อ “แก้ปมด้อย” ของ MATCH โดยเฉพาะ 😎
.
ที่เจ๋งคือ XMATCH ใช้ค่า Default เป็น Exact Match ให้เลย ไม่เหมือน MATCH ที่ Default เป็น Approximate Match แล้วมักทำให้เจอ bug บ่อยๆ 😅
.
นอกจากนี้ยังรองรับการค้นหาย้อนกลับ (หาข้อมูลล่าสุด), Binary Search สำหรับข้อมูลขนาดใหญ่ (เร็วมากกกก 🚀), และการค้นหาแบบ Wildcard ด้วย
.
ส่วนตัวผมคิดว่า XMATCH เป็นหนึ่งในฟังก์ชันที่ดีที่สุดใน Excel 365 เลยครับ ยืดหยุ่นและปลอดภัยกว่า MATCH หลายเท่า 💡