เมื่อไหร่ควรใช้ INDEX-MATCH แทน VLOOKUP ใน Excel

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

หากคุณเพิ่งเริ่มเรียนรู้ VLOOKUP สามารถอ่านบทความก่อนหน้าของผมเกี่ยวกับวิธีใช้ VLOOKUP ใน Excel ได้

แม้ VLOOKUP จะทรงพลัง แต่ก็มีข้อจำกัดเกี่ยวกับโครงสร้างตารางอ้างอิงที่ต้องจัดเรียงให้ถูกต้องจึงจะทำงานได้

บทความนี้จะแสดงข้อจำกัดที่ VLOOKUP ใช้ไม่ได้ และแนะนำฟังก์ชัน INDEX-MATCH ที่สามารถแก้ปัญหานี้ได้

ตัวอย่างการใช้ INDEX MATCH ใน Excel

จากตัวอย่างไฟล์ Excel ด้านล่าง เรามีรายชื่อเจ้าของรถและชื่อรถยนต์ ในที่นี้เราจะลองดึง Car ID โดยอิงจาก Car Model ที่แสดงภายใต้เจ้าของหลายคน ดังภาพ:

บนแผ่นงานชื่อ CarType มีฐานข้อมูลรถยนต์ง่ายๆ ประกอบด้วย ID, Car Model และ Color

ด้วยโครงสร้างตารางนี้ VLOOKUP จะทำงานได้ก็ต่อเมื่อข้อมูลที่ต้องการดึงอยู่ในคอลัมน์ทางขวาของคอลัมน์ที่จับคู่ (Car Model)

กล่าวคือ เนื่องจากเราจับคู่ด้วย Car Model ข้อมูลที่ดึงได้คือ Color เท่านั้น (ไม่ใช่ ID เพราะคอลัมน์ ID อยู่ทางซ้ายของ Car Model)

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

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

แต่ในบทความนี้จะพูดถึงการรวมทั้งสองฟังก์ชันเพื่อเลียนแบบการทำงานของ VLOOKUP เท่านั้น

สูตรดูยาวและซับซ้อนตอนแรก แต่ใช้ไปใช้มาจะจดจำไวยากรณ์ได้เอง

สูตรเต็มในตัวอย่างคือ:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

การแยกวิเคราะห์แต่ละส่วน:

• =INDEX( – เครื่องหมาย = เริ่มต้นสูตร และ INDEX เป็นฟังก์ชันแรกที่ใช้
• CarType!$A$2:$A$5 – ช่วงคอลัมน์บนแผ่น CarType ที่มีข้อมูลที่ต้องการดึง (ในที่นี้คือ ID ของแต่ละ Car Model)
• MATCH( – ฟังก์ชันที่สองที่ใช้ร่วมกับ INDEX
• B4 – เซลล์ที่มีข้อความค้นหา (Car Model)
• CarType!$B$2:$B$5 – ช่วงคอลัมน์บนแผ่น CarType ที่มีข้อมูลสำหรับจับคู่กับข้อความค้นหา
• 0)) – ระบุให้จับคู่แบบแม่นยำ (Exact Match) หากไม่พบจะคืนค่า #N/A

หมายเหตุ: จงจำไว้ว่าต้องมีวงเล็บปิดคู่ท้าย )) และคั่นอาร์กิวเมนต์ด้วยเครื่องหมายจุลภาค

ส่วนตัวผมเลิกใช้ VLOOKUP และเปลี่ยนมาใช้ INDEX-MATCH แทนเพราะมันทำได้มากกว่า

ฟังก์ชัน INDEX-MATCH มีข้อดีเพิ่มเติมเหนือ VLOOKUP ดังนี้:

1. คำนวณเร็วกว่า

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

2. ไม่ต้องนับคอลัมน์สัมพัทธ์

ถ้าตารางอ้างอิงมีคีย์ค้นหาอยู่คอลัมน์ C แต่อยากดึงข้อมูลจากคอลัมน์ AQ VLOOKUP จะต้องนับว่ามีกี่คอลัมน์ระหว่าง C ถึง AQ

แต่ INDEX-MATCH เราสามารถเลือกคอลัมน์ Index (คอลัมน์ AQ) ที่อยากดึงข้อมูล และคอลัมน์ Match (คอลัมน์ C) โดยตรงได้เลย

3. ดูซับซ้อนกว่า (ทำให้ดูเก่ง)

VLOOKUP เป็นที่รู้จักกันดี แต่คนไม่มากที่รู้จัก INDEX-MATCH สูตรที่ยาวกว่าช่วยให้คุณดูเหมือนผู้เชี่ยวชาญ Excel ระดับสูง สนุกไปกับมันเถอะ!