ใครที่เคยใช้ VLOOKUP มาก่อน คงเจอปัญหากวนใจอยู่บ้าง เช่น หาข้อมูลที่อยู่ทางซ้ายของคอลัมน์ที่ค้นหาไม่ได้ หรือย้ายคอลัมน์แล้วสูตรพัง วันนี้จะพาไปรู้จักคู่สูตรคลาสสิกที่แก้ปัญหาพวกนี้ได้หมด นั่นคือ INDEX + MATCH เทคนิคที่มืออาชีพใช้กันมานาน และยังจำเป็นอยู่แม้ Excel รุ่นใหม่จะมี XLOOKUP แล้วก็ตาม
INDEX คืออะไร
INDEX คือสูตรที่ดึงค่าจากตำแหน่งที่ระบุในช่วงข้อมูล โดยบอกแค่ “แถวที่เท่าไหร่ คอลัมน์ที่เท่าไหร่” สูตรก็จะไปหยิบค่าตรงจุดนั้นมาให้ รูปแบบคือ
=INDEX(array, row_num, [column_num])
- array คือช่วงข้อมูลที่ต้องการดึงผลลัพธ์ออกมา
- row_num คือลำดับแถวภายในช่วงนั้น (นับจากแถวแรกของ array เป็นแถวที่ 1)
- column_num คือลำดับคอลัมน์ ถ้า array มีคอลัมน์เดียวไม่ต้องใส่ก็ได้
จุดสำคัญคือ INDEX เองไม่ได้ “ค้นหา” อะไรเลย มันแค่หยิบค่าตามตำแหน่งที่บอกเท่านั้น ปัญหาคือเราไม่รู้ล่วงหน้าว่าค่าที่ต้องการอยู่แถวไหน นี่คือจุดที่ MATCH เข้ามาช่วย
MATCH คืออะไร
MATCH คือสูตรที่ค้นหาตำแหน่ง (ลำดับที่) ของค่าหนึ่งในช่วงข้อมูล แล้วคืนกลับมาเป็นตัวเลขลำดับ ไม่ใช่ค่าจริง รูปแบบคือ
=MATCH(lookup_value, lookup_array, [match_type])
- lookup_value คือค่าที่ต้องการค้นหาตำแหน่ง
- lookup_array คือช่วงที่จะค้นหา
- match_type ใส่ 0 เพื่อค้นหาแบบตรงเป๊ะ (exact match) ซึ่งเป็นค่าที่ควรใช้เกือบทุกกรณีในชีวิตจริง
เช่น ถ้า A1:A5 คือรายชื่อสินค้า แล้วใช้ =MATCH("คีย์บอร์ด",A1:A5,0) ผลลัพธ์ที่ได้จะเป็นตัวเลขลำดับแถวที่เจอคำว่า “คีย์บอร์ด” เช่น 3 ไม่ใช่ราคาหรือข้อมูลอื่นใดๆ
รวม INDEX + MATCH เข้าด้วยกัน
ทีนี้พอเอา MATCH ไปใส่แทนตำแหน่งแถวใน INDEX ก็จะได้สูตรค้นหาที่ยืดหยุ่นกว่า VLOOKUP มาก รูปแบบมาตรฐานคือ
=INDEX(ช่วงผลลัพธ์, MATCH(ค่าที่หา, ช่วงค้นหา, 0))
ความหมายคือ ให้ MATCH หาก่อนว่าค่าที่ต้องการอยู่แถวไหน แล้วส่งเลขแถวนั้นให้ INDEX ไปหยิบผลลัพธ์จากช่วงที่กำหนดมาอีกที ข้อดีที่ต่างจาก VLOOKUP ชัดเจนคือ ค้นหาไปทางซ้ายได้ เพราะ INDEX กับ MATCH ไม่สนใจว่าคอลัมน์ผลลัพธ์อยู่ซ้ายหรือขวาของคอลัมน์ค้นหา ขอแค่ระบุช่วงให้ถูกก็พอ
ตัวอย่างใช้งานจริง: หาราคาสินค้าที่ชื่ออยู่ทางขวาราคาอยู่ทางซ้าย
สมมติมีตารางที่คอลัมน์ A เป็นราคา คอลัมน์ B เป็นชื่อสินค้า (ราคาอยู่ซ้ายมือของชื่อ) กรณีนี้ VLOOKUP ทำไม่ได้ตรงๆ เพราะ VLOOKUP ค้นหาได้แค่จากคอลัมน์ซ้ายสุดไปทางขวาเท่านั้น แต่ INDEX + MATCH ทำได้สบายๆ
=INDEX(A2:A4,MATCH(D2,B2:B4,0))| A | B | D | E | |
|---|---|---|---|---|
| 1 | ราคา | ชื่อสินค้า | ค้นหา | ผลลัพธ์ |
| 2 | 590 | คีย์บอร์ด | เมาส์ไร้สาย | 350 |
| 3 | 350 | เมาส์ไร้สาย | ||
| 4 | 1,200 | หูฟังบลูทูธ |
สูตรนี้ให้ MATCH หาก่อนว่า “เมาส์ไร้สาย” อยู่แถวที่เท่าไหร่ในช่วง B2:B4 (พบว่าเป็นแถวที่ 2) แล้วส่งเลข 2 นี้ให้ INDEX ไปหยิบค่าจากช่วง A2:A4 แถวที่ 2 ซึ่งก็คือ 350 ได้ผลลัพธ์ถูกต้องแม้ราคาจะอยู่คอลัมน์ซ้ายของชื่อสินค้าก็ตาม
ตัวอย่างใช้งานจริง: ค้นหาแบบสองมิติ (สองทาง) พร้อมกัน
อีกจุดเด่นของ INDEX + MATCH ที่ VLOOKUP ทำไม่ได้ในสูตรเดียวคือการค้นหาแบบสองมิติ เช่น หาคะแนนของนักเรียนคนหนึ่งในวิชาหนึ่ง โดยต้องระบุทั้งชื่อและวิชาพร้อมกัน วิธีทำคือใช้ MATCH สองตัวซ้อนกัน ตัวหนึ่งหาแถว อีกตัวหาคอลัมน์
=INDEX(B2:C4,MATCH(E2,A2:A4,0),MATCH(F2,B1:C1,0))| A | B | C | |
|---|---|---|---|
| 1 | ชื่อ | คณิตศาสตร์ | ภาษาอังกฤษ |
| 2 | น้องพลอย | 85 | 78 |
| 3 | น้องกันต์ | 72 | 90 |
| 4 | น้องมิว | 95 | 88 |
| E | F | G | |
|---|---|---|---|
| 1 | ชื่อที่หา | วิชาที่หา | คะแนน |
| 2 | น้องกันต์ | ภาษาอังกฤษ | 90 |
MATCH ตัวแรกหาว่า “น้องกันต์” อยู่แถวที่เท่าไหร่ในคอลัมน์ชื่อ (แถวที่ 2 ของช่วง A2:A4) ส่วน MATCH ตัวที่สองหาว่า “ภาษาอังกฤษ” อยู่คอลัมน์ที่เท่าไหร่ในหัวตาราง (คอลัมน์ที่ 2 ของช่วง B1:C1) แล้ว INDEX ก็หยิบค่าจากช่วง B2:C4 ตามแถวและคอลัมน์ที่ MATCH ทั้งสองตัวหาให้ ได้คะแนน 90 ตรงตามจริง
แล้ว XLOOKUP ล่ะ ยังต้องใช้ INDEX + MATCH อยู่ไหม
ถ้าใครอ่านบทความ XLOOKUP ต่างจาก VLOOKUP ยังไง มาแล้ว จะรู้ว่า XLOOKUP ในปัจจุบันค้นหาไปทางซ้ายได้เหมือนกันและเขียนง่ายกว่า ถ้าใช้ Excel เวอร์ชันใหม่ที่รองรับ XLOOKUP อยู่แล้วก็แนะนำให้ใช้ XLOOKUP เพราะสั้นและอ่านง่ายกว่า แต่ INDEX + MATCH ยังจำเป็นอยู่ในสองกรณีหลักคือ ไฟล์ที่ต้องเปิดด้วย Excel รุ่นเก่าที่ยังไม่มี XLOOKUP และงานที่ต้องค้นหาแบบสองมิติพร้อมกันแบบตัวอย่างด้านบน ซึ่งเขียนด้วย INDEX + MATCH ได้กระชับกว่า
ข้อควรระวังเวลาใช้ INDEX + MATCH
- match_type ควรเป็น 0 เสมอ เกือบทุกกรณีใช้งานจริงต้องการค้นหาแบบตรงเป๊ะ ถ้าเผลอไม่ใส่หรือใส่ผิด สูตรอาจได้ผลลัพธ์ที่ดูเหมือนถูกแต่จริงๆ คลาดเคลื่อน
- ขนาดช่วงต้องสอดคล้องกัน array ของ INDEX กับ lookup_array ของ MATCH ต้องมีจำนวนแถว (หรือคอลัมน์) เท่ากัน ถ้าช่วงไม่ตรงกันสูตรจะขึ้น
#REF!หรือดึงค่าผิดแถวโดยไม่มีข้อความเตือน - ระวังแถวหัวตาราง เวลานับ row_num ให้นับจากแถวแรกของ array ที่เลือกไว้ ไม่ใช่นับจากแถวที่ 1 ของทั้งชีท ถ้าเผลอรวมแถวหัวเข้าไปในช่วงจะทำให้ตำแหน่งเลื่อนไปหนึ่งแถว
สรุป
INDEX + MATCH คือคู่สูตรที่ยืดหยุ่นกว่า VLOOKUP เพราะค้นหาได้ทั้งซ้ายและขวา แถมยังทำการค้นหาแบบสองมิติได้ในสูตรเดียว แม้ Excel รุ่นใหม่จะมี XLOOKUP ที่ใช้งานง่ายกว่า แต่ถ้าไฟล์ต้องเปิดในโปรแกรมรุ่นเก่าหรือมีงานที่ต้องค้นหาสองทางพร้อมกัน INDEX + MATCH ก็ยังเป็นตัวเลือกที่ควรมีติดตัวไว้ ใครยังไม่คุ้นกับสูตรค้นหาพื้นฐาน แนะนำให้อ่าน XLOOKUP ต่างจาก VLOOKUP ยังไง และ วิธีใช้ IFERROR ดักจับ Error ควบคู่กันไปด้วย จะช่วยให้ทำงานกับตารางข้อมูลได้คล่องขึ้นมาก
