วิธีใช้ฟังก์ชัน INDEX และ MATCH ใน Excel

สิ่งที่ควรรู้

• ฟังก์ชัน INDEX สามารถใช้ได้เพียงอย่างเดียว แต่การซ้อนฟังก์ชัน MATCH เข้าไปภายใน จะสร้างการค้นหาขั้นสูง (Advanced Lookup)
• ฟังก์ชันซ้อนนี้ยืดหยุ่นกว่า VLOOKUP และสามารถให้ผลลัพธ์ได้เร็วกว่า

บทความนี้อธิบายวิธีใช้ฟังก์ชัน INDEX และ MATCH ร่วมกันในทุกเวอร์ชันของ Excel รวมถึง Excel 2019 และ Microsoft 365

ฟังก์ชัน INDEX และ MATCH คืออะไร?

INDEX และ MATCH เป็นฟังก์ชัน Lookup ของ Excel แม้จะเป็นฟังก์ชันที่แยกจากกันสองตัว และสามารถใช้งานได้แยกกัน แต่ก็สามารถรวมกันเพื่อสร้างสูตรขั้นสูงได้

ฟังก์ชัน INDEX คืนค่าหรือการอ้างอิงถึงค่าจากภายในช่วงที่เลือก ตัวอย่างเช่น สามารถใช้หาค่าในแถวที่สองของชุดข้อมูล หรือแถวที่ห้าคอลัมน์ที่สาม

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

ไวยากรณ์และอาร์กิวเมนต์ของ INDEX และ MATCH

นี่คือวิธีเขียนทั้งสองฟังก์ชันเพื่อให้ Excel เข้าใจ:

=INDEX(array, row_num, [column_num])

• array คือช่วงเซลล์ที่สูตรจะใช้ สามารถเป็นหนึ่งหรือหลายแถวและคอลัมน์ เช่น A1:D5 จำเป็นต้องมี
• row_num คือแถวใน array ที่จะคืนค่า เช่น 2 หรือ 18 จำเป็นต้องมี ยกเว้นกรณีมี column_num
• column_num คือคอลัมน์ใน array ที่จะคืนค่า เช่น 1 หรือ 9 ไม่จำเป็น

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value คือค่าที่คุณต้องการจับคู่ใน lookup_array สามารถเป็นตัวเลข ข้อความ หรือค่าตรรกะ ที่พิมพ์ด้วยมือหรืออ้างอิงผ่านเซลล์ จำเป็นต้องมี
• lookup_array คือช่วงเซลล์ที่จะค้นหา สามารถเป็นแถวเดียวหรือคอลัมน์เดียว เช่น A2:D2 หรือ G1:G45 จำเป็นต้องมี
• match_type สามารถเป็น -1, 0, หรือ 1 ระบุวิธีจับคู่ lookup_value กับค่าใน lookup_array (ดูตารางด้านล่าง) 1 เป็นค่าเริ่มต้นหากละเว้นอาร์กิวเมนต์นี้

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

ตัวอย่างสูตร INDEX และ MATCH

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

ตัวอย่าง INDEX

=INDEX(A1:B2,2,2)
=INDEX(A1:B1,1)
=INDEX(2:2,1)
=INDEX(B1:B2,1)

ในตัวอย่างแรกนี้ มีสูตร INDEX 4 สูตรที่เราสามารถใช้เพื่อดึงค่าต่างๆ:

• =INDEX(A1:B2,2,2) ค้นหาใน A1:B2 เพื่อหาค่าในคอลัมน์ที่สอง แถวที่สอง ซึ่งคือ Stacy
• =INDEX(A1:B1,1) ค้นหาใน A1:B1 เพื่อหาค่าในคอลัมน์แรก ซึ่งคือ Jon
• =INDEX(2:2,1) ค้นหาทุกอย่างในแถวที่สอง เพื่อหาค่าในคอลัมน์แรก ซึ่งคือ Tim
• =INDEX(B1:B2,1) ค้นหาใน B1:B2 เพื่อหาค่าในแถวแรก ซึ่งคือ Amy

ตัวอย่าง MATCH

=MATCH("Stacy",A2:D2,0)
=MATCH(14,D1:D2)
=MATCH(14,D1:D2,-1)
=MATCH(13,A1:D1,0)

นี่คือตัวอย่างง่ายๆ 4 ตัวของฟังก์ชัน MATCH:

• =MATCH("Stacy",A2:D2,0) ค้นหา Stacy ในช่วง A2:D2 และคืนค่า 3 เป็นผลลัพธ์
• =MATCH(14,D1:D2) ค้นหา 14 ในช่วง D1:D2 แต่ไม่พบในตาราง MATCH จึงหาค่า ถัดไป ที่ใหญ่ที่สุดแต่น้อยกว่าหรือเท่ากับ 14 ซึ่งในกรณีนี้คือ 13 อยู่ตำแหน่ง 1 ของ lookup_array
• =MATCH(14,D1:D2,-1) เหมือนสูตรข้างต้น แต่เนื่องจาก array ไม่ได้เรียงจากมากไปน้อยตามที่ -1 กำหนด จึงเกิดข้อผิดพลาด
• =MATCH(13,A1:D1,0) ค้นหา 13 ในแถวแรกของชีต ซึ่งคืนค่า 4 เนื่องจากเป็นรายการที่สี่ใน array นี้

ตัวอย่าง INDEX-MATCH

นี่คือตัวอย่าง 2 กรณีที่เราสามารถรวม INDEX และ MATCH ในสูตรเดียว:

ค้นหาการอ้างอิงเซลล์ในตาราง

=INDEX(B2:B5,MATCH(F1,A2:A5))

ตัวอย่างนี้ซ้อนสูตร MATCH ภายในสูตร INDEX เป้าหมายคือระบุสีของสินค้าโดยใช้หมายเลขสินค้า

หาก你看圖片 在 "Separated" แถว สูตรจะเขียนแยกกัน แต่เนื่องจากเราซ้อนกัน จึงเกิดเหตุการณ์ดังนี้:

• MATCH(F1,A2:A5) ค้นหาค่า F1 (8795) ในชุดข้อมูล A2:A5 หากนับลงมาตามคอลัมน์ จะเห็นว่าอยู่ที่ 2 นั่นคือสิ่งที่ MATCH หาได้
• Array ของ INDEX คือ B2:B5 เนื่องจากเรากำลังหาค่าในคอลัมน์นั้น
• ฟังก์ชัน INDEX ตอนนี้สามารถเขียนใหม่ได้ว่า เนื่องจาก 2 คือสิ่งที่ MATCH หาได้: INDEX(B2:B5, 2, [column_num])
• เนื่องจาก column_num ไม่จำเป็น เราสามารถลบออกได้ ทำให้เหลือ: INDEX(B2:B5,2)
• ตอนนี้เหมือนสูตร INDEX ธรรมดา ที่หาค่าของรายการที่สองใน B2:B5 ซึ่งคือ red

ค้นหาตามหัวแถวและหัวคอลัมน์

=INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0))

ในตัวอย่าง MATCH และ INDEX นี้ เราทำ Two-way Lookup (ค้นหา 2 ทิศทาง) เป้าหมายคือดูว่าเราทำเงินจากสินค้า Green ในเดือน May ได้เท่าไหร่ นี่คล้ายตัวอย่างก่อนหน้า แต่มีสูตร MATCH เพิ่มอีกอันซ้อนใน INDEX

• MATCH(G1,A2:A13,0) คือรายการแรกที่แก้ในสูตรนี้ ค้นหา G1 (คำว่า "May") ใน A2:A13 เพื่อหาค่าเฉพาะ เราไม่เห็นที่นี่แต่มันคือ 5
• MATCH(G2,B1:E1,0) คือสูตร MATCH ที่สอง คล้ายตัวแรก แต่ค้นหา G2 (คำว่า "Green") ในหัวคอลัมน์ที่ B1:E1 ตัวนี้ได้ 3
• ตอนนี้เราสามารถเขียนสูตร INDEX ใหม่ได้เพื่อให้เห็นภาพว่าเกิดอะไรขึ้น: =INDEX(B2:E13,5,3) นี่คือค้นหาในตารางทั้งหมด B2:E13 สำหรับแถวที่ห้าและคอลัมน์ที่สาม ซึ่งคืนค่า $180

กฎของ MATCH และ INDEX

มีหลายอย่างที่ต้องจำเมื่อเขียนสูตรด้วยฟังก์ชันเหล่านี้:

• MATCH ไม่สนใจตัวพิมพ์เล็ก/ใหญ่ (Case-insensitive) ตัวพิมพ์ใหญ่และเล็กถูกจัดการเหมือนกันเมื่อจับคู่ค่าข้อความ
• MATCH คืนค่า #N/A หลายเหตุผล: หาก match_type เป็น 0 และ lookup_value ไม่พบ หาก match_type เป็น -1 และ lookup_array ไม่ได้เรียงจากมากไปน้อย หาก match_type เป็น 1 และ lookup_array ไม่ได้เรียงจากน้อยไปมาก และหาก lookup_array ไม่ใช่แถวเดี่ยวหรือคอลัมน์เดี่ยว
• คุณสามารถใช้ตัวอักษรตัวแทน (Wildcard) ในอาร์กิวเมนต์ lookup_value ได้ หาก match_type เป็น 0 และ lookup_value เป็นสตริงข้อความ เครื่องหมายคำถามจับคู่ตัวอักษรเดี่ยวได้ และดอกจัน (*) จับคู่ลำดับตัวอักษรได้ (เช่น =MATCH("Jo*",1:1,0)) หากต้องการใช้ MATCH หาคำถามหรือดอกจันจริง ให้พิมพ์ ~ ก่อน
• INDEX คืนค่า #REF! หาก row_num และ column_num ไชี้ไปเซลล์ที่อยู่นอก array

ฟังก์ชัน Excel ที่เกี่ยวข้อง

ฟังก์ชัน MATCH คล้ายกับ LOOKUP แต่ MATCH คืน ตำแหน่ง ของรายการ แทนที่จะเป็นรายการเอง

VLOOKUP เป็นฟังก์ชัน Lookup อีกตัวที่ใช้ใน Excel ได้ แต่ไม่เหมือน MATCH ที่ต้องใช้ INDEX สำหรับการค้นหาขั้นสูง VLOOKUP ต้องการแค่ฟังก์ชันเดียว