เหตุใด INDEX-MATCH จึงดีกว่า VLOOKUP

VLOOKUP ค้นหาในคอลัมน์แรกของตารางและส่งคืนค่าจากคอลัมน์ทางขวา โดยระบุคอลัมน์ด้วยหมายเลข

ตัวอย่างเช่น =VLOOKUP(E1, A2:D20, 3, FALSE) จะค้นหา E1 ในคอลัมน์ A และส่งคืนค่าจากคอลัมน์ที่ 3 ของตาราง

ฟังก์ชันนี้เป็นที่นิยมและใช้งานง่าย แต่ก็มีข้อจำกัดที่สำคัญอยู่หลายประการ ดังที่คุณจะเห็นว่า INDEX-MATCH ช่วยหลีกเลี่ยงข้อจำกัดเหล่านี้ได้ทั้งหมด

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

หากรหัสของคุณอยู่ในคอลัมน์ C และชื่อที่ต้องการอยู่ในคอลัมน์ A VLOOKUP ก็จะไม่สามารถทำงานได้

INDEX-MATCH ไม่มีกฎเช่นนั้น =INDEX(A2:A20, MATCH(E1, C2:C20, 0)) ค้นหาในคอลัมน์ C และส่งคืนข้อมูลจากคอลัมน์ A ได้โดยไม่ต้องใช้วิธีแก้ขัด

อาร์กิวเมนต์ตัวที่ 3 ของ VLOOKUP คือหมายเลขคอลัมน์ที่กำหนดตายตัว เช่นเลข 3 ใน =VLOOKUP(E1, A2:D20, 3, FALSE)

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

INDEX-MATCH อ้างอิงคอลัมน์จริงด้วยช่วงข้อมูล ดังนั้นเมื่อแทรกคอลัมน์ การอ้างอิงจะเลื่อนตามโดยอัตโนมัติ และผลลัพธ์ยังคงถูกต้อง

เนื่องจาก INDEX ชี้ไปยังช่วงคอลัมน์เฉพาะ เช่น C2:C20 การอ้างอิงนั้นจึงเลื่อนตามคอลัมน์เมื่อโครงสร้างเปลี่ยนแปลง

เมื่อแทรกคอลัมน์ใหม่ไว้ด้านหน้า สเปรดชีตจะอัปเดต C2:C20 เป็น D2:D20 ให้เอง สูตรจึงยังคงส่งคืนฟิลด์เดิม

ความทนทานนี้สำคัญกับเวิร์กบุ๊กจริงที่มีหลายคนแก้ไขอยู่ตลอดเวลา ข้อผิดพลาดที่ไม่แสดงสัญญาณเตือนน้อยลงหมายถึงรายงานที่น่าเชื่อถือมากขึ้น

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

INDEX-MATCH แตะต้องเพียงสองคอลัมน์แคบ ๆ ได้แก่คอลัมน์ที่ใช้ค้นหาและคอลัมน์ที่ส่งคืนข้อมูล

สำหรับสูตรไม่กี่สูตร ความแตกต่างอาจมองไม่เห็น แต่เมื่อมีการค้นหาหลายพันครั้ง INDEX-MATCH อาจคำนวณใหม่ได้เร็วขึ้นอย่างเห็นได้ชัด

หากต้องการดึงหลายฟิลด์ด้วย VLOOKUP คุณต้องเขียนสูตรทั้งหมดซ้ำและเปลี่ยนหมายเลขคอลัมน์ทุกครั้ง ซึ่งเป็นจุดที่เกิดข้อผิดพลาดได้ง่าย

เมื่อใช้ INDEX-MATCH คุณจะคำนวณตำแหน่งเพียงครั้งเดียวแล้วนำกลับมาใช้ซ้ำได้ หลายคนเก็บ =MATCH(E1, A2:A20, 0) ไว้ในเซลล์ช่วย เช่น H1 แล้วเขียน =INDEX(C2:C20, H1) และ =INDEX(D2:D20, H1)

ค้นหาครั้งเดียว ดึงข้อมูลได้หลายรายการอย่างเป็นระเบียบ

สเปรดชีตรุ่นใหม่มี XLOOKUP ซึ่งค้นหาได้ทุกทิศทางและไม่ประสบปัญหาเรื่องหมายเลขคอลัมน์ จึงแก้ปัญหาเดียวกับ INDEX-MATCH ได้

=XLOOKUP(E1, A2:A20, C2:C20) มีรูปแบบสะอาดตาและอ่านเข้าใจง่าย

อย่างไรก็ตาม XLOOKUP ไม่มีใน Excel รุ่นเก่าหรือเวิร์กบุ๊กที่ใช้ร่วมกันบางรายการ ส่วน INDEX-MATCH ใช้งานได้เกือบทุกที่ จึงยังเป็นทักษะสำคัญ

หากมองอย่างเป็นธรรม INDEX-MATCH ก็มีข้อเสียอยู่หนึ่งอย่าง คือมีข้อความยาวกว่าและอ่านผ่าน ๆ ได้ยากกว่า VLOOKUP

ลองเปรียบเทียบ =VLOOKUP(E1, A2:D20, 3, FALSE) กับ =INDEX(C2:C20, MATCH(E1, A2:A20, 0))

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

นี่คือการเขียนการค้นหาเดียวกันด้วยสองวิธี สำหรับตารางที่มีชื่ออยู่ใน A และเงินเดือนอยู่ใน D:

• VLOOKUP: =VLOOKUP(E1, A2:D20, 4, FALSE)
• INDEX-MATCH: =INDEX(D2:D20, MATCH(E1, A2:A20, 0))

ทั้งสองวิธีส่งคืนเงินเดือนเหมือนกัน แต่หากมีการแทรกคอลัมน์ จะมีเพียง INDEX-MATCH เท่านั้นที่ยังคงถูกต้อง และมีเพียงวิธีนี้ที่สามารถส่งคืนค่าจากทางซ้ายของคอลัมน์ A ได้

หลักง่าย ๆ ที่นำไปใช้ได้จริง:

• ใช้ XLOOKUP เมื่อสเปรดชีตของคุณรองรับ เพื่อให้ได้รูปแบบการเขียนสมัยใหม่ที่สะอาดที่สุด
• ใช้ INDEX-MATCH เมื่อต้องการความเข้ากันได้สูงสุด การค้นหาไปทางซ้าย และการอ้างอิงที่ไม่เสียหายเมื่อมีการแทรกคอลัมน์
• ใช้ VLOOKUP เฉพาะกับการค้นหาอย่างรวดเร็วและเรียบง่ายทางขวาของคีย์ในตารางที่มีโครงสร้างคงที่

เมื่อรู้จัก INDEX-MATCH คุณก็จะสามารถอ่านและแก้ไขเวิร์กบุ๊กเดิมจำนวนนับไม่ถ้วนที่ใช้งานฟังก์ชันนี้ได้

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

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

เมื่อเชี่ยวชาญส่วนประกอบพื้นฐาน คุณก็จะก้าวข้ามข้อจำกัดของฟังก์ชันค้นหาใดฟังก์ชันหนึ่งได้

เลือกข้อได้เปรียบที่ INDEX-MATCH มีเหนือ VLOOKUP

คุณได้เปรียบเทียบแนวทางทั้งสองและเห็นข้อได้เปรียบของ INDEX-MATCH:

• ค้นหาได้ทุกทิศทาง รวมถึงทางซ้ายของคีย์
• การอ้างอิงคอลัมน์ยังคงใช้ได้เมื่อมีการแทรกหรือย้ายคอลัมน์
• อาจทำงานได้เร็วกว่าเพราะอ่านเฉพาะสองคอลัมน์ที่จำเป็น
• ทำงานได้ในสเปรดชีตเก่าที่ไม่มี XLOOKUP

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