Thep Excel
XMATCHฟังก์ชันค้นหาตำแหน่งข้อมูลขั้นสูง
XMATCH คืนค่าตำแหน่งของข้อมูลที่ค้นหาในช่วงหรืออาร์เรย์ ถือเป็นฟังก์ชันรุ่นใหม่ที่ Microsoft พัฒนามาแก้จุดอ่อนของ MATCH โดยเฉพาะเรื่องค่า Default ที่เป็น Exact Match แทน Approximate Match ทำให้ใช้ง่ายและปลอดภัยกว่ามาก นอกจากนี้ยังรองรับการค้นหาย้อนกลับ Binary Search สำหรับข้อมูลขนาดใหญ่ และการค้นหาแบบ Wildcard ทำให้ยืดหยุ่นกว่า MATCH เดิมหลายเท่า
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| lookup_value | any | ค่าที่ต้องการค้นหา สามารถเป็นตัวเลข ข้อความ วันที่ หรือค่าตรรกะ | |
| lookup_array | range | ช่วงหรืออาร์เรย์ที่ต้องการค้นหา ต้องเป็นช่วงแถวหรือคอลัมน์เดียว | |
| [match_mode]ไม่บังคับ | number | 0 | โหมดการจับคู่: 0 = ตรงทั้งหมด (ค่าเริ่มต้น) -1 = ตรงทั้งหมด หรือค่าที่เล็กกว่าที่ใกล้เคียงที่สุด 1 = ตรงทั้งหมด หรือค่าที่ใหญ่กว่าที่ใกล้เคียงที่สุด 2 = ค้นหาแบบ Wildcard โดย * ? และ ~ มีความหมายพิเศษ |
| [search_mode]ไม่บังคับ | number | 1 | โหมดการค้นหา: 1 = ค้นหาจากต้นไปท้าย (ค่าเริ่มต้น) -1 = ค้นหาจากท้ายไปต้น (ย้อนกลับ) 2 = ค้นหาแบบไบนารีจากน้อยไปมาก (ต้องเรียงข้อมูลจากน้อยไปมาก) -2 = ค้นหาแบบไบนารีจากมากไปน้อย (ต้องเรียงข้อมูลจากมากไปน้อย) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
XMATCH คือฟังก์ชันค้นหาตำแหน่งที่ Microsoft สร้างมาเพื่อ “แก้ปมด้อย” ของ MATCH โดยเฉพาะ 😎
ที่เจ๋งคือ XMATCH ใช้ค่า Default เป็น Exact Match ให้เลย ไม่เหมือน MATCH ที่ Default เป็น Approximate Match แล้วมักทำให้เจอ bug บ่อยๆ 😅
นอกจากนี้ยังรองรับการค้นหาย้อนกลับ (หาข้อมูลล่าสุด), Binary Search สำหรับข้อมูลขนาดใหญ่ (เร็วมากกกก 🚀), และการค้นหาแบบ Wildcard ด้วย
ส่วนตัวผมคิดว่า XMATCH เป็นหนึ่งในฟังก์ชันที่ดีที่สุดใน Excel 365 เลยครับ ยืดหยุ่นและปลอดภัยกว่า MATCH หลายเท่า 💡
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
| A | B | C | D | |
|---|---|---|---|---|
| 1 | รหัสสินค้า | ชื่อสินค้า | สถานะ | ราคา |
| 2 | P001 | เมาส์ไร้สาย | ปิด | 590 |
| 3 | P002 | คีย์บอร์ด | เปิด | 1,290 |
| 4 | P003 | จอ 27 นิ้ว | ปิด | 8,900 |
| 5 | P004 | หูฟัง | เปิด | 2,450 |
| 6 | P005 | เว็บแคม | ปิด | 1,750 |
ทุกสูตรด้านล่างอ้างถึงตารางนี้ พิมพ์ตามได้เลย ผลลัพธ์จะตรงกับที่เขียนไว้
| A | B | |
|---|---|---|
| 1 | คะแนนขั้นต่ำ | เกรด |
| 2 | 0 | F |
| 3 | 50 | D |
| 4 | 60 | C |
| 5 | 70 | B |
| 6 | 80 | A |
ใช้กับ match_mode/search_mode แบบ Binary Search ซึ่งบังคับว่าคอลัมน์แรกต้องเรียงจากน้อยไปมาก
ตัวอย่างที่ 1: ค้นหาตำแหน่งสินค้าจากรหัสXMATCH("P004", A2:A6)
สูตรค้นหารหัส "P004" ในคอลัมน์รหัสสินค้า A2:A6 และคืนค่าตำแหน่งที่ 4 เพราะ P004 อยู่ลำดับที่ 4 ของช่วง
ที่เจ๋งคือ XMATCH ทำ Exact Match ให้อัตโนมัติ ไม่ต้องมานั่งกังวลเหมือน MATCH ที่ Default เป็น Approximate Match แล้วได้ผลลัพธ์ผิดๆ
ไม่ต้องใส่ match_mode เลยด้วยซ้ำครับ เพราะ 0 (Exact Match) เป็นค่าเริ่มต้นอยู่แล้ว
ตัวอย่างที่ 2: ใช้ร่วมกับ INDEX เพื่อค้นหาชื่อสินค้าINDEX(B2:B6, XMATCH("P004", A2:A6))
สูตรทำงาน 2 ขั้นตอน ขั้นแรก XMATCH ค้นหารหัส "P004" ในคอลัมน์ A2:A6 และคืนค่าตำแหน่งที่ 4
ขั้นที่สอง INDEX ดึงค่าจากคอลัมน์ชื่อสินค้า B2:B6 ที่ตำแหน่งเดียวกัน ได้ "หูฟัง"
คอมโบนี้แทน VLOOKUP ได้เลยครับ แถมยังยืดหยุ่นกว่าอีก เพราะค้นหาได้ทุกทิศทาง ไม่จำกัดว่าข้อมูลต้องอยู่ทางซ้ายหรือขวา
ตัวอย่างที่ 3: ค้นหาย้อนกลับเพื่อหาแถวสุดท้ายที่ตรงเงื่อนไขXMATCH("ปิด", C2:C6, 0, -1)
ในตารางมีสถานะ "ปิด" อยู่ 3 แถว คือ P001, P003 และ P005 ถ้าค้นหาแบบปกติจะได้ตำแหน่ง 1 เพราะเจอตัวบนสุดก่อน
แต่พอใส่ search_mode = -1 มันจะไล่ค้นหาจากล่างขึ้นบนแทน จึงเจอ "ปิด" ตัวล่างสุดที่ตำแหน่ง 5 (แถวของ P005)
ฟีเจอร์นี้เจ๋งมากครับ MATCH เดิมทำไม่ได้แบบนี้ ต้องมาใช้ Array Formula ซับซ้อน แต่ XMATCH ทำให้ง่ายขึ้นเยอะเลย
ตัวอย่างที่ 4: ค้นหาแบบ WildcardXMATCH("จอ*", B2:B6, 2)
สูตรใช้ match_mode = 2 เพื่อค้นหาแบบ Wildcard ในคอลัมน์ชื่อสินค้า B2:B6
"จอ*" หมายถึงชื่อที่ขึ้นต้นด้วย "จอ" ตามด้วยอักขระใดก็ได้ สูตรคืนค่า 3 คือตำแหน่งที่พบ "จอ 27 นิ้ว"
ใช้ Wildcard pattern ได้เหมือน COUNTIF หรือ SUMIF เลยครับ * แทนอักษรกี่ตัวก็ได้ ส่วน ? แทนอักษร 1 ตัว
ตัวอย่างที่ 5: ค้นหาเร็วด้วย Binary SearchXMATCH(50, F2:F6, 0, 2)
คอลัมน์คะแนนขั้นต่ำ F2:F6 เรียงจากน้อยไปมากอยู่แล้ว (0, 50, 60, 70, 80) จึงใช้ search_mode = 2 เพื่อค้นหาแบบ Binary Search ได้
สูตรค้นหาค่า 50 เจอที่ตำแหน่งที่ 2 พอดี ผลลัพธ์เหมือน search_mode ปกติ แต่ Binary Search จะเร็วกว่ามากเมื่อข้อมูลมีหลักพันหลักหมื่นแถว เพราะไม่ต้องไล่ดูทีละแถว
ข้อควรระวังคือต้องแน่ใจว่าข้อมูลเรียงลำดับอย่างถูกต้อง มิฉะนั้นจะได้ผลลัพธ์ผิดโดยไม่มี error ขึ้นเตือน
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
ฟังก์ชันที่ผู้เขียนโยงไว้กับ XMATCH จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
HLOOKUP ค้นหาข้อมูลจากแถวแรกของตาราง แล้วคืนค่าจากแถวที่ระบุ ตรงข้ามกับ VLOOKUP ที่ค้นหาแนวตั้ง เหมาะสำหรับตารางที่หัวข้อมูลเรียงแบบแนวนอน
INDEX คืนค่าหรือ reference ของเซลล์จากตำแหน่งที่ระบุใน range หรือ array โดยอ้างอิงจากหมายเลขแถวและคอลัมน์ที่เราระบุ มักถูกใช้ร่วมกับ MATCH เพื่อสร้าง INDEX-MATCH pattern ที่ยืดหยุ่นกว่า VLOOKUP เยอะ เพราะสามารถดึงข้อมูลจากทิศทางไหนก็ได้ และไม่พังเมื่อมีการแทรกหรือลบคอลัมน์
MATCH คืนเลขลำดับตำแหน่งของค่าที่ค้นหาในช่วงข้อมูลแถวเดียวหรือคอลัมน์เดียว รองรับการค้นหา 3 โหมด คือ Exact Match (0) ที่ไม่ต้องเรียงข้อมูล, Approximate Match แบบ Less Than or Equal (1) ที่ต้องเรียงจากน้อยไปมาก, และ Greater Than or Equal (-1) ที่ต้องเรียงจากมากไปน้อย รองรับ Wildcard (* และ ?) ในโหมด Exact Match และมักใช้คู่กับ INDEX เป็นรูปแบบ INDEX-MATCH ที่ยืดหยุ่นกว่า VLOOKUP
SORTBY เรียงลำดับข้อมูลตาม Array อื่นที่กำหนด รองรับหลายระดับการเรียง (multi-level) และสามารถกำหนดลำดับเอง (custom sort order) ด้วย XMATCH ต่างจาก SORT ที่เรียงตามคอลัมน์ภายในตัวเอง SORTBY ใช้คอลัมน์ภายนอกเป็นเกณฑ์ได้
VLOOKUP เป็นฟังก์ชันค้นหาแนวตั้งที่ได้รับความนิยมสูงสุดใน Excel โดยค้นหาค่าที่ต้องการในคอลัมน์ซ้ายสุดของตาราง จากนั้นดึงข้อมูลจากคอลัมน์ที่ระบุในแถวเดียวกัน รองรับทั้งการค้นหาแบบตรงทุกตัวอักษร (Exact Match) และแบบประมาณค่า (Approximate Match) สำหรับข้อมูลที่เรียงลำดับ ข้อจำกัดหลักคือต้องค้นหาจากคอลัมน์ซ้ายสุดเท่านั้น ไม่สามารถดึงข้อมูลจากด้านซ้ายของคอลัมน์ค้นหาได้
ฟังก์ชัน XLOOKUP ใช้สำหรับค้นหาข้อมูลในตารางทั้งแนวตั้งและแนวนอน มีความยืดหยุ่นสูงกว่า VLOOKUP โดยสามารถค้นหาจากซ้ายไปขวา ขวาไปซ้าย และกำหนดค่าเริ่มต้นเมื่อไม่พบข้อมูลได้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่