INDEX MATCH: สุดยอดฟังก์ชันค้นหาข้อมูลใน Excel

หากคุณใช้ VLOOKUP สำหรับงานค้นหาข้อมูลใน Excel อยู่เป็นประจำ คุณไม่ใช่คนเดียวที่ทำเช่นนั้น เพราะมันคือหนึ่งในฟังก์ชันที่เป็นที่รู้จักมากที่สุดในโลกของสเปรดชีต แต่ผู้ใช้ Excel ที่มีประสบการณ์มักจะเปลี่ยนมาใช้ INDEX MATCH ซึ่งเป็นการรวมกันของสองฟังก์ชันที่ให้ความยืดหยุ่นกว่า เสถียรกว่า และสามารถแก้ปัญหาที่ VLOOKUP ไม่สามารถทำได้ บทความนี้จะอธิบายว่าทำไมถึงเป็นเช่นนั้น พร้อมตัวอย่างไวยากรณ์ การนำไปใช้จริง และวิธีทำทีละขั้นตอนที่คุณสามารถทำตามได้ทันที

INDEX และ MATCH คืออะไร?

ก่อนที่จะนำมาใช้งานร่วมกัน การทำความเข้าใจแต่ละฟังก์ชันแยกกันจะช่วยให้เข้าใจได้ง่ายขึ้น

ฟังก์ชัน INDEX

INDEX จะส่งคืนค่าของเซลล์ที่อยู่ในตำแหน่งที่ระบุภายในช่วงข้อมูล (Range) หรืออาร์เรย์ (Array)

=INDEX(array, row_num, [col_num])

• array — ช่วงของเซลล์ที่ต้องการค้นหาข้อมูล
• row_num — ตำแหน่งแถวภายในช่วงข้อมูลนั้น
• col_num — (ไม่บังคับ) ตำแหน่งคอลัมน์ภายในช่วงข้อมูลนั้น

ตัวอย่างเช่น =INDEX(A1:A10, 3) จะส่งคืนค่าใดๆ ก็ตามที่อยู่ในแถวที่สามของคอลัมน์ A (จากแถวที่ 1 ถึงแถวที่ 10)

ฟังก์ชัน MATCH

MATCH จะค้นหาค่าที่กำหนดภายในช่วงข้อมูลแล้วส่งคืน หมายเลขตำแหน่ง ของค่านั้น — ไม่ใช่ส่งคืนตัวค่าของมันเอง แต่เป็นตัวเลขที่บอกว่าค่านั้นอยู่ที่ตำแหน่งใด

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value — ค่าที่คุณต้องการค้นหา
• lookup_array — แถวหรือคอลัมน์เดี่ยวๆ ที่ต้องการค้นหา
• match_type — ใช้ 0 สำหรับการค้นหาแบบตรงกันทุกประการ (พบบ่อยที่สุด), 1 สำหรับค่าน้อยกว่า, -1 สำหรับค่ามากกว่า

ตัวอย่างเช่น หาก A1:A5 มีข้อมูล {Apple, Banana, Cherry, Date, Fig} สูตร =MATCH("Cherry", A1:A5, 0) จะส่งคืนค่า 3 เนื่องจาก Cherry เป็นรายการที่สาม

การใช้ INDEX และ MATCH ร่วมกัน

พลังที่แท้จริงจะปรากฏขึ้นเมื่อคุณซ้อน MATCH ไว้ใน INDEX แทนที่จะระบุหมายเลขแถวแบบตายตัว คุณสามารถให้ MATCH คำนวณตำแหน่งแบบไดนามิกได้ดังนี้:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

สูตรนี้เป็นการบอกให้ Excel: "ค้นหาตำแหน่งของค่าที่ต้องการใน lookup range จากนั้นส่งคืนค่าที่ตรงกันในบรรทัดเดียวกันจาก return range" ช่วงข้อมูลทั้งสองช่วงจะต้องมีขนาดเท่ากันและอยู่ในทิศทางเดียวกัน

วิธีทำและตัวอย่างการใช้งานจริง

ลองจินตนาการถึงตารางสินค้าคงคลังที่มีโครงสร้างดังนี้:

รหัสสินค้า	ชื่อสินค้า	หมวดหมู่	ราคาต่อหน่วย	จำนวนในคลัง
P-101	Wireless Mouse	Electronics	$29.99	142
P-102	USB-C Hub	Electronics	$49.99	87
P-103	Desk Lamp	Office	$34.99	55
P-104	Notebook A5	Stationery	$8.99	310
P-105	Ergonomic Chair	Furniture	$299.00	12

ข้อมูลอยู่ใน A2:E6 โดยมีหัวตารางในแถวที่ 1 หากคุณต้องการค้นหา 'ราคาต่อหน่วย' ของสินค้าที่มีรหัสระบุอยู่ในเซลล์ H2

ด้วยการใช้ INDEX MATCH สูตรในช่อง H3 จะเป็นดังนี้:

=INDEX($D$2:$D$6, MATCH(H2, $A$2:$A$6, 0))

อธิบายทีละขั้นตอน:

1. MATCH จะค้นหาค่าที่อยู่ในเซลล์ H2 จากคอลัมน์ A (รหัสสินค้า)
2. มันจะส่งคืนตำแหน่งแถวที่พบ — เช่น ส่งคืนค่า 3 ถ้า H2 ระบุเป็น "P-103"
3. จากนั้น INDEX จะส่งคืนค่าที่อยู่ในแถวที่ 3 ของคอลัมน์ D (ราคาต่อหน่วย) ซึ่งก็คือ $34.99

สังเกตการใช้ การอ้างอิงเซลล์แบบสัมบูรณ์ (Absolute cell references) ด้วยเครื่องหมายดอลลาร์ การล็อกช่วงข้อมูลจะทำให้มั่นใจได้ว่าสูตรจะยังคงทำงานได้อย่างถูกต้องหากคุณคัดลอกสูตรไปยังเซลล์อื่น

ทำไม INDEX MATCH ถึงดีกว่า VLOOKUP

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

1. ค้นหาไปทางซ้าย — VLOOKUP ทำไม่ได้ แต่ INDEX MATCH ทำได้

VLOOKUP จะค้นหาข้อมูลจากคอลัมน์ซ้ายสุดของตารางเท่านั้น แล้วส่งคืนค่าที่อยู่ทางขวา หากคอลัมน์ที่คุณต้องการค้นหาอยู่ทางขวาของคอลัมน์ผลลัพธ์ VLOOKUP จะไม่สามารถทำงานได้ ในทางกลับกัน INDEX MATCH ไม่มีข้อจำกัดนี้ — ช่วงผลลัพธ์ (return range) และช่วงค้นหา (lookup range) จะแยกจากกันอย่างสิ้นเชิง คุณจึงสามารถดึงค่าจากคอลัมน์ใดก็ได้ รวมถึงคอลัมน์ที่อยู่ทางซ้ายของคอลัมน์ที่ใช้ค้นหาข้อมูลด้วย

2. สูตรไม่พังแม้มีการเปลี่ยนลำดับคอลัมน์

VLOOKUP ใช้การระบุหมายเลขคอลัมน์แบบตายตัว (เช่น คอลัมน์ที่สาม) หากมีการแทรกหรือลบคอลัมน์ หมายเลขนั้นก็จะผิดไป และส่งคืนข้อมูลที่ไม่ถูกต้องโดยที่คุณไม่รู้ตัว แต่เนื่องจาก INDEX MATCH อ้างอิงช่วงเซลล์จริงๆ การแทรกคอลัมน์จึงไม่ทำให้สูตรพังเลย

3. ทำงานเร็วกว่าในชุดข้อมูลขนาดใหญ่

VLOOKUP จะสแกนตารางข้อมูล (table array) ทั้งหมดในทุกๆ การคำนวณ ส่วน INDEX MATCH จะประเมินผลเฉพาะในคอลัมน์ค้นหาและคอลัมน์ผลลัพธ์ที่ระบุเท่านั้น ซึ่งทำให้การประมวลผลเร็วกว่าอย่างเห็นได้ชัดในสมุดงาน (Workbook) ที่มีข้อมูลหลายหมื่นแถว

4. การค้นหาแบบสองทิศทาง (2D Lookups)

คุณสามารถซ้อนฟังก์ชัน MATCH สองตัว — ตัวหนึ่งสำหรับแถว และอีกตัวหนึ่งสำหรับคอลัมน์ — เพื่อสร้างการค้นหาแบบสองมิติ ซึ่ง VLOOKUP ไม่สามารถทำได้หากไม่ใช้สูตรตัวช่วย (helper formulas):

=INDEX(B2:E6, MATCH(H2, A2:A6, 0), MATCH(H3, B1:E1, 0))

ในกรณีนี้ MATCH(H2, A2:A6, 0) จะหาแถวที่ถูกต้อง และ MATCH(H3, B1:E1, 0) จะหาคอลัมน์ที่ถูกต้อง เพียงเปลี่ยนเซลล์อินพุตตัวใดตัวหนึ่ง สูตรก็จะปรับเปลี่ยนและแสดงผลลัพธ์ทันที เทคนิคนี้มีประโยชน์อย่างมากสำหรับ แดชบอร์ดการขาย (Sales dashboards) ที่คุณจำเป็นต้องดึงตัวชี้วัดจากหลายๆ มิติ

การจัดการกับข้อผิดพลาดอย่างเหมาะสม

เมื่อไม่พบข้อมูลที่ตรงกัน MATCH จะส่งคืนค่าข้อผิดพลาด #N/A คุณสามารถครอบสูตร INDEX MATCH ทั้งหมดด้วย IFERROR เพื่อแสดงข้อความที่เข้าใจง่ายขึ้นแทนได้:

=IFERROR(INDEX($D$2:$D$6, MATCH(H2, $A$2:$A$6, 0)), "Product not found")

สิ่งนี้สำคัญมากในสมุดงาน (Workbook) ที่ใช้งานร่วมกัน หรือในเทมเพลตที่ผู้ใช้งานระบบปลายทางเป็นผู้พิมพ์ค่าที่ต้องการค้นหาลงไป — การจัดการข้อผิดพลาดอย่างเป็นระเบียบจะช่วยป้องกันความสับสนและความหงุดหงิดได้ หากนำไปใช้คู่กับ การตรวจสอบความถูกต้องของข้อมูล (Data validation) บนเซลล์อินพุตเพื่อจำกัดการป้อนข้อมูลให้อยู่ในรายการที่ถูกต้องเท่านั้น คุณก็จะได้เครื่องมือค้นหาข้อมูลที่แข็งแกร่งและป้องกันความผิดพลาดจากผู้ใช้ (User-proof) ได้เป็นอย่างดี

การค้นหาด้วยเงื่อนไขหลายข้อ (Multiple Criteria) ด้วย INDEX MATCH

หนึ่งในสถานการณ์ที่มีการถามหากันมากที่สุด คือการค้นหาข้อมูลด้วยเงื่อนไขมากกว่าหนึ่งข้อ สมมติว่าคุณต้องการค้นหา 'ราคาต่อหน่วย' ที่ 'หมวดหมู่' เป็น "Electronics" และ 'จำนวนในคลัง' มีน้อยกว่า 100 คุณสามารถทำได้ด้วย INDEX MATCH เวอร์ชันอาร์เรย์ (Array)

=INDEX($D$2:$D$6, MATCH(1, ($C$2:$C$6="Electronics")*($E$2:$E$6<100), 0))

ใน Excel เวอร์ชันเก่า (ก่อนรุ่น 365) คุณต้องกด Ctrl + Shift + Enter เพื่อป้อนสูตรนี้เป็นสูตรอาร์เรย์ — ซึ่ง Excel จะใส่เครื่องหมายปีกกา {} ครอบสูตรไว้ให้ ใน Excel 365 และ Excel 2021 ฟีเจอร์ Dynamic Arrays จะจัดการเรื่องนี้ให้โดยอัตโนมัติ การกดปุ่ม Enter ตามปกติจึงเพียงพอแล้ว

หลักการทำงาน: แต่ละเงื่อนไขจะสร้างอาร์เรย์ของค่า TRUE/FALSE (1 และ 0) การคูณมันเข้าด้วยกันจะสร้างอาร์เรย์ใหม่ขึ้นมา ซึ่งจะมีค่าเป็น 1 เฉพาะในกรณีที่ทั้งสองเงื่อนไขเป็น TRUE (เป็นจริง) จากนั้น MATCH จะค้นหาค่า 1 ตัวแรก และ INDEX จะส่งคืนราคาที่ตรงกันออกมา

INDEX MATCH เทียบกับ XLOOKUP

Excel 365 ได้เปิดตัว XLOOKUP ซึ่งช่วยให้งานค้นหาข้อมูลหลายอย่างง่ายขึ้นได้ด้วยฟังก์ชันเดียว XLOOKUP นั้นยอดเยี่ยมสำหรับการค้นหาข้อมูลแบบตรงไปตรงมา และรองรับการค้นหาไปทางซ้ายโดยตัวมันเองอยู่แล้ว อย่างไรก็ตาม INDEX MATCH ก็ยังคงมีความสำคัญด้วยเหตุผลหลายประการ:

• มันทำงานได้ใน Excel ทุกเวอร์ชัน รวมถึง Excel 2010, 2013, 2016 และ 2019 — ไม่ว่าคุณจะแชร์สมุดงานไปให้ใครใช้งานก็ตาม
• รูปแบบการค้นหาแบบสองมิติด้วยการซ้อนฟังก์ชัน MATCH ยังคงกระชับและเขียนง่ายกว่าทางเลือกอื่นใน XLOOKUP
• โมเดลทางการเงินและเทมเพลตการตรวจสอบระดับมืออาชีพจำนวนมากถูกสร้างขึ้นมาบนพื้นฐานของ INDEX MATCH ดังนั้นการจะอ่านและดูแลรักษาเทมเพลตเหล่านั้น คุณจำเป็นต้องมีความคุ้นเคยกับการใช้ฟังก์ชันคู่กันนี้

ความเข้าใจใน INDEX MATCH ยังเป็นพื้นฐานสำคัญเมื่อต้องทำงานขั้นสูง เช่น การสร้างแดชบอร์ดแบบไดนามิกใน Excel ซึ่งต้องใช้สูตรค้นหาข้อมูลเพื่อป้อนข้อมูลให้แผนภูมิและตารางสรุปที่อัปเดตอัตโนมัติ

เคล็ดลับง่ายๆ เพื่อการใช้ INDEX MATCH ให้สำเร็จ

• ใช้ 0 เป็น match_type เสมอ สำหรับการค้นหาแบบตรงกันทุกประการ (Exact match) เว้นแต่คุณจะมีเหตุผลเฉพาะเจาะจงที่ต้องใช้การค้นหาแบบค่าใกล้เคียง
• ล็อกช่วงข้อมูลของคุณ ด้วยการอ้างอิงแบบสัมบูรณ์ (เครื่องหมาย $) เพื่อให้สามารถคัดลอกสูตรข้ามแถวหรือคอลัมน์ได้อย่างถูกต้อง
• รักษาความยาวของ lookup range และ return range ให้เท่ากันเสมอ — ขนาดที่ไม่ตรงกันจะส่งผลให้ผลลัพธ์ออกมาผิดพลาดได้
• ตั้งชื่อช่วงข้อมูล (Name Ranges) โดยใช้ Name Box (ช่องทางซ้ายของแถบสูตร) เพื่อให้อ่านง่ายขึ้น: =INDEX(UnitPrices, MATCH(H2, ProductIDs, 0)) จะตรวจสอบง่ายกว่าการอ้างอิงเซลล์แบบธรรมดามาก
• ใช้งานร่วมกับ IF และ SUMIFS สำหรับการค้นหาแบบมีเงื่อนไข — ดูคู่มือของเราเกี่ยวกับ SUMIF และ SUMIFS สำหรับเทคนิคเพิ่มเติมที่เกี่ยวข้องกับการใช้สูตรแบบมีเงื่อนไข

สร้างสูตร INDEX MATCH ในพริบตา

หากคุณกำลังปวดหัวกับความต้องการในการค้นหาข้อมูลที่ซับซ้อน — ทั้งเงื่อนไขหลายข้อ โครงสร้างตารางที่ไม่เป็นมาตรฐาน หรือการอ้างอิงข้อมูลข้ามแผ่นงาน — คุณสามารถอธิบายสิ่งที่คุณต้องการเป็นภาษาทั่วไปให้ GPTExcel ฟัง และรับสูตร INDEX MATCH ที่พร้อมใช้งานได้ภายในไม่กี่วินาที พร้อมทั้งการอ้างอิงเซลล์แบบสัมบูรณ์ที่ถูกต้องและการจัดการข้อผิดพลาด ซึ่งจะช่วยขจัดการเดาสุ่ม และช่วยให้คุณได้สูตรที่ทำงานได้จริงโดยไม่ต้องมานั่งลองผิดลองถูกเอง

สำหรับเทคนิคการเขียนสูตรที่กว้างขึ้นด้วยการใช้ AI บทความเรื่อง การใช้ ChatGPT เพื่อเขียนสูตร Excel ได้ครอบคลุมขั้นตอนการทำงานไว้อย่างละเอียด

คำถามที่พบบ่อย (FAQ)

INDEX MATCH ดีกว่า VLOOKUP เสมอไปหรือไม่?

สำหรับกรณีการใช้งานระดับมืออาชีพส่วนใหญ่ คำตอบคือ ใช่ INDEX MATCH สามารถจัดการกับการค้นหาข้อมูลไปทางซ้ายได้ ไม่พังเมื่อมีการแทรกคอลัมน์ใหม่ และรองรับการค้นหาแบบสองมิติและแบบหลายเงื่อนไข VLOOKUP นั้นเขียนง่ายกว่าก็จริงสำหรับการค้นหาข้อมูลไปทางขวาแบบพื้นฐาน แต่ข้อจำกัดของมันจะสร้างความปวดหัวให้เมื่อข้อมูลของคุณมีความซับซ้อนมากขึ้น

ฉันจำเป็นต้องกด Ctrl+Shift+Enter สำหรับ INDEX MATCH หรือไม่?

คุณต้องกดคีย์ลัดนี้เฉพาะเมื่อใช้งานสูตรเวอร์ชันอาร์เรย์แบบหลายเงื่อนไขใน Excel 2019 หรือเวอร์ชันเก่ากว่าเท่านั้น สำหรับสูตร INDEX MATCH แบบเงื่อนไขเดียวมาตรฐาน สามารถกดปุ่ม Enter ตามปกติได้ใน Excel ทุกเวอร์ชัน และใน Excel 365 รวมถึง Excel 2021 ที่มีฟีเจอร์ Dynamic Arrays นั้น แม้แต่เวอร์ชันหลายเงื่อนไขก็ไม่จำเป็นต้องใช้คีย์ลัดอาร์เรย์อีกต่อไป

จะเกิดอะไรขึ้นหากมีค่าซ้ำกันในคอลัมน์ค้นหา (Lookup column) ของฉัน?

MATCH จะส่งคืนตำแหน่งของข้อมูล ตัวแรก ที่ค้นพบเสมอ หากคอลัมน์ค้นหาของคุณมีข้อมูลที่ซ้ำกัน และคุณจำเป็นต้องดึงข้อมูลของแต่ละรายการออกมา ให้พิจารณาใช้คอลัมน์ผู้ช่วย (Helper column) ที่มีการเชื่อมคีย์เข้าด้วยกัน หรือใช้ Power Query — ซึ่งมีอธิบายไว้ใน คู่มือการใช้ Power Query ของเรา — เพื่อจัดโครงสร้างข้อมูลใหม่ก่อนที่จะทำการค้นหา

INDEX MATCH สามารถค้นหาข้อมูลข้ามแผ่นงาน (Sheet) ได้หรือไม่?

ได้แน่นอน เพียงแค่ใส่ชื่อแผ่นงาน (Sheet name) เข้าไปในการอ้างอิงช่วงข้อมูลของคุณ ตัวอย่างเช่น: =INDEX(Sheet2!$D$2:$D$100, MATCH(H2, Sheet2!$A$2:$A$100, 0)) สูตรจะทำงานได้เหมือนกันทุกประการ ไม่ว่าช่วงข้อมูลจะอยู่ในแผ่นงานเดียวกัน หรือคนละแผ่นงานภายในสมุดงาน (Workbook) เดียวกันก็ตาม