สอนใช้ INDEX + MATCH แบบสั้นๆ เท่าที่จำเป็น (แถม XLOOKUP)

ถ้าถามว่าฟังก์ชันในการ Lookup ข้อมูลอันไหนที่ฮิตที่สุด คำตอบก็คงไม่พ้น VLOOKUP แต่ถ้าถามว่าอะไรยืดหยุ่นที่สุดและใช้ได้กับ Excel ทุก Version คำตอบก็คือ INDEX+MATCH นั่นเอง (แต่ถ้าคุณมี Excel 365 ที่มีพวก XLOOKUP กับ XMATCH มันก็จะเจ๋งกว่าอ่ะนะ 555)

ในบทความนี้ผมจะมาสอนการใช้งานเจ้า INDEX กับ MATCH แบบสั้นๆ ไม่ได้เจาะลึกอะไรมากมาย (ถ้าจะลงลึกเรื่อง INDEX อ่านได้ที่นี่) แต่สามารถเอาไปใช้ได้เลยละกันนะครับ เน้นเอาใจคนมีเวลาน้อย อิอิ

ทำไม VLOOKUP อย่างเดียวจึงไม่เพียงพอ?

ผมว่ามันเป็นเพราะ 2 สาเหตุใหญ่ๆ เลย คือ

1. VLOOKUP ค้นหาแล้ววิ่งไปทางซ้ายไม่ได้
2. VLOOKUP จะเสี่ยงต่อการสูตรพังเมื่อมีการแทรกคอลัมน์ในตารางอ้างอิง (เพราะระบุ Col_index_num เป็นตัวเลขค่าคงที่ในสูตร)

ด้วย 2 สาเหตุนี้ คงจะเพียงพอที่จะให้คุณลองอยากจะเรียนรู้ INDEX+MATCH ขึ้นมาบ้างแล้วล่ะ ซึ่งข่าวดีคือ ผมว่ามันใช้ง่ายมากๆ

หน้าที่ของ INDEX และ MATCH

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

จะเห็นว่า INDEX ดึงข้อมูลได้ แต่ค้นหาข้อมูลเองไม่ได้ ส่วน MATCH ค้นหาข้อมูลได้ ดึงข้อมูลไม่ได้ มันจึงเกิดมาคู่กันนั่นเอง

วิธีการใช้งาน INDEX + MATCH

version วีดีโอ

version บทความ

สถานการณ์คือ จะหาว่า sales ค ชื่อว่าอะไร แต่ชื่อดันอยู่คอลัมน์ซ้ายมือ เลยใช้ VLOOKUP ไม่ได้

วิธีแก้ปัญหาคือ เราต้องใช้ MATCH หาให้ได้ก่อนว่า sales ค อยู่ลำดับที่เท่าไหร่ แล้วเราค่อยใช้ INDEX ดึงชื่อลำดับนั้นๆ ออกมา

การใช้ MATCH มีการใช้งานดังนี้

=MATCH(lookup_value,lookup_array,match_type)
โดยที่ match_type แบบ Exact Match คือให้ระบุเป็นเลข 0

ดังนั้นลำดับตำแหน่งของ sales ค หาได้ดังนี้

=MATCH(A8,B2:B5,0)
=3

จากนั้น ค่อยใช้ INDEX ดึงค่าใน range ที่ระบุ ในตำแหน่งที่ต้องการออกมา

=INDEX(array,row_num,column_num)
แต่เราใส่แค่นี้ได้ ถ้าเราเลือกพื้นที่ array แค่มิติเดียว (คอลัมน์เดียว หรือ แถวเดียว)
=INDEX(array,ลำดับตำแหน่งของตัวที่ต้องการ)
=INDEX(array,ลำดับตำแหน่งจาก MATCH)

ดังนั้นชื่อที่เราต้องการ สามารถใช้สูตรได้ดังนี้

=INDEX(A2:A5,ตำแหน่งที่หาได้จาก MATCH)

พอผสมกันก็จะเป็นดังนี้

=INDEX(A2:A5,MATCH(A8,B2:B5,0))

หรือเขียนในรูปแบบทั่วๆ ไปก็คือ

=INDEX(return_array,MATCH(lookup_value,lookup_array,0))
=INDEX(คอลัมน์ผลลัพธ์ที่ต้องการ,MATCH(lookup_value,คอลัมน์ที่เอา lookup_value ไปค้นหา,,0))

สรุปแล้วผมสามารถเชียนได้ดังนี้

แค่นี้เพื่อนๆ ก็ใช้ INDEX+MATCH หาข้อมูลที่ต้องการได้แล้วล่ะครับ ซึ่งไม่ต้องกลัวเรื่องวิ่งไปซ้ายไปขวา หรือไม่ต้องกลัวการแทรกคอลัมน์ในตารางอ้างอิงอีกต่อไป เย้ๆ

แถมเรื่อง XLOOKUP

ถ้าเรามี XLOOKUP ก็จะเขียนสูตรได้ประมาณนี้ คือเลือก lookup_array,return_array ได้เลย ดังนั้นใครมี XLOOKUP ก็จะง่ายกว่าแหละ แต่ถ้าไม่มีก็ต้องใช้ INDEX+MATCH ไปนะครับ ^^

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
=XLOOKUP(lookup_value,lookup_array,return_array)
=XLOOKUP(lookup_value,คอลัมน์ที่เอา lookup_value ไปค้นหา,คอลัมน์ผลลัพธ์ที่ต้องการ)
=XLOOKUP(A8,B2:B5,A2:A5)