ต้องรู้
- ฟังก์ชัน INDEX สามารถใช้คนเดียวได้ แต่การซ้อนฟังก์ชัน MATCH ไว้ภายในจะสร้างการค้นหาขั้นสูง
- ฟังก์ชันที่ซ้อนกันนี้มีความยืดหยุ่นมากกว่า VLOOKUP และสามารถให้ผลลัพธ์ได้เร็วกว่า
บทความนี้อธิบายวิธีใช้ฟังก์ชัน INDEX และ MATCH ร่วมกันใน Excel ทุกเวอร์ชัน รวมถึง Excel 2019 และ Microsoft 365
ฟังก์ชัน INDEX และ MATCH คืออะไร
INDEX และ MATCH คือฟังก์ชันการค้นหาของ Excel แม้ว่าจะเป็น 2 ฟังก์ชันที่แยกจากกันโดยสิ้นเชิง แต่ก็สามารถนำมารวมกันเพื่อสร้างสูตรขั้นสูงได้
ฟังก์ชัน INDEX ส่งคืนค่าหรือการอ้างอิงไปยังค่าจากภายในการเลือกเฉพาะ ตัวอย่างเช่น สามารถใช้เพื่อค้นหาค่าในแถวที่สองของชุดข้อมูล หรือในแถวที่ห้าและคอลัมน์ที่สาม
ในขณะที่ INDEX สามารถใช้ได้เพียงลำพัง การซ้อน MATCH ในสูตรทำให้มีประโยชน์มากขึ้นอีกเล็กน้อย ฟังก์ชัน MATCH จะค้นหารายการที่ระบุในช่วงของเซลล์ จากนั้นส่งคืนตำแหน่งสัมพัทธ์ของรายการในช่วง ตัวอย่างเช่น สามารถใช้เพื่อกำหนดว่าชื่อเฉพาะคือรายการที่สามในรายชื่อ
INDEX และ MATCH Syntax & Arguments
นี่คือวิธีที่ต้องเขียนทั้งสองฟังก์ชันเพื่อให้ Excel เข้าใจ:
=INDEX(array, row_num, [column_num])
- array คือช่วงของเซลล์ที่สูตรจะใช้ อาจเป็นแถวและคอลัมน์ได้ตั้งแต่หนึ่งแถวขึ้นไป เช่น A1:D5 จำเป็น
- row_num คือแถวในอาร์เรย์ที่จะคืนค่า เช่น 2 หรือ 18 จำเป็นต้องระบุ เว้นแต่จะมี column_num
- column_num คือคอลัมน์ในอาร์เรย์ที่จะคืนค่า เช่น 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 เป็นค่าเริ่มต้นถ้าอาร์กิวเมนต์นี้ถูกละเว้น
| ประเภทการจับคู่ที่จะใช้ | |||
|---|---|---|---|
| ประเภทการแข่งขัน | มันทำอะไร | Rule | ตัวอย่าง |
| 1 | ค้นหาค่าที่ใหญ่ที่สุดที่น้อยกว่าหรือเท่ากับ lookup_value. | ค่า lookup_array ต้องเรียงลำดับจากน้อยไปหามาก (เช่น -2, -1, 0, 1, 2; หรือ A-Z; หรือ FALSE, TRUE | lookup_value คือ 25 แต่ไม่มี lookup_array ดังนั้นตำแหน่งของตัวเลขที่น้อยที่สุดถัดไป เช่น 22 จะถูกส่งคืนแทน |
| 0 | ค้นหาค่าแรกที่เท่ากับ lookup_value ทุกประการ | ค่า lookup_array สามารถอยู่ในลำดับใดก็ได้ | lookup_value คือ 25 จึงส่งคืนตำแหน่ง 25 |
| -1 | ค้นหาค่าที่น้อยที่สุดที่มากกว่าหรือเท่ากับ lookup_value. | ค่า lookup_array ต้องเรียงลำดับจากมากไปหาน้อย (เช่น 2, 1, 0, -1, -2) | lookup_value คือ 25 แต่หายไปจาก lookup_array ดังนั้นตำแหน่งของตัวเลขที่ใหญ่ที่สุดถัดไป เช่น 34 จะถูกส่งคืนแทน |
ใช้ 1 หรือ -1 สำหรับเวลาที่คุณต้องการเรียกใช้การค้นหาโดยประมาณตามมาตราส่วน เช่น เมื่อต้องจัดการกับตัวเลขและเมื่อการประมาณนั้นใช้ได้ แต่อย่าลืมว่าถ้าคุณไม่ระบุ match_type 1 จะเป็นค่าดีฟอลต์ ซึ่งอาจบิดเบือนผลลัพธ์ได้หากคุณต้องการให้ตรงกันทุกประการ
ตัวอย่าง INDEX และ MATCH Formulas
ก่อนที่เราจะมาดูวิธีรวม INDEX และ MATCH ให้เป็นสูตรเดียว เราต้องเข้าใจว่าฟังก์ชันเหล่านี้ทำงานอย่างไร
ตัวอย่างดัชนี
=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=ดัชนี(B1:B2, 1)
Image ในตัวอย่างแรกนี้มีสี่สูตร INDEX ที่เราสามารถใช้เพื่อให้ได้ค่าที่แตกต่างกัน:
- =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("สเตซี่", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)
Image นี่คือสี่ตัวอย่างง่ายๆ ของฟังก์ชัน 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) เหมือนกับสูตรด้านบน แต่เนื่องจากอาร์เรย์ไม่เรียงลำดับจากมากไปน้อยเช่น -1 ต้องการ เราได้รับข้อผิดพลาด
- =MATCH(13, A1:D1, 0) กำลังมองหา 13 ในแถวแรกของชีต ซึ่งจะคืนค่า 4 เนื่องจากเป็นรายการที่สี่ในอาร์เรย์นี้
ตัวอย่าง INDEX-MATCH
นี่คือสองตัวอย่างที่เราสามารถรวม INDEX และ MATCH ไว้ในสูตรเดียวได้:
ค้นหาการอ้างอิงเซลล์ในตาราง
=INDEX(B2:B5, MATCH(F1, A2:A5))
ตัวอย่างนี้ซ้อนสูตร MATCH ภายในสูตร INDEX เป้าหมายคือการระบุสีของสินค้าโดยใช้หมายเลขสินค้า
ถ้าคุณดูภาพ คุณจะเห็นในแถว "แยก" ว่าสูตรจะเขียนด้วยตัวมันเองอย่างไร แต่เนื่องจากเรากำลังซ้อนพวกมันอยู่ นี่คือสิ่งที่กำลังเกิดขึ้น:
- MATCH(F1, A2:A5) กำลังมองหาค่า F1 (8795) ในชุดข้อมูล A2:A5 ถ้าเรานับถอยหลังคอลัมน์ เราจะเห็นว่าเป็น 2 นั่นคือสิ่งที่ฟังก์ชัน MATCH เพิ่งคิดออก
- อาร์เรย์ INDEX คือ B2:B5 เพราะเรากำลังมองหาค่าในคอลัมน์นั้นในที่สุด
- ฟังก์ชัน INDEX สามารถเขียนใหม่แบบนี้ได้ เนื่องจาก 2 คือสิ่งที่ MATCH พบ: INDEX(B2:B5, 2, [column_num]).
- เนื่องจาก column_num เป็นทางเลือก เราสามารถลบให้เหลือสิ่งนี้ได้: INDEX(B2:B5, 2).
- ตอนนี้ก็เหมือนกับสูตร INDEX ปกติที่เราจะหาค่าของรายการที่สองใน B2:B5 ซึ่งเป็นสีแดง
ค้นหาตามแถวและส่วนหัวของคอลัมน์
=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))
ในตัวอย่าง MATCH และ INDEX นี้ เรากำลังค้นหาแบบสองทาง แนวคิดคือการดูว่าเราทำเงินได้เท่าไรจากสินค้าสีเขียวในเดือนพฤษภาคม สิ่งนี้คล้ายกับตัวอย่างด้านบนจริงๆ แต่มีสูตร MATCH พิเศษซ้อนอยู่ใน INDEX
- MATCH(G1, A2:A13, 0) เป็นรายการแรกที่ได้รับการแก้ไขในสูตรนี้ กำลังมองหา G1 (คำว่า "พฤษภาคม") ใน A2:A13 เพื่อรับค่าเฉพาะ เราไม่เห็นมันที่นี่ แต่มันคือ 5.
- MATCH(G2, B1:E1, 0) เป็นสูตร MATCH ที่สอง และคล้ายกับสูตรแรกมาก แต่กำลังมองหา G2 แทน (คำว่า "สีเขียว") ในส่วนหัวของคอลัมน์ที่ B1:E1 อันนี้แก้ไขเป็น 3.
- ตอนนี้เราสามารถเขียนสูตร INDEX ใหม่ได้แบบนี้เพื่อให้เห็นภาพว่าเกิดอะไรขึ้น: =INDEX(B2:E13, 5, 3) นี่คือการดูทั้งตาราง B2:E13 สำหรับแถวที่ห้าและคอลัมน์ที่สามซึ่งส่งคืน $180.
กฎการแข่งขันและดัชนี
มีหลายสิ่งที่ควรคำนึงถึงเมื่อเขียนสูตรด้วยฟังก์ชันเหล่านี้:
- MATCH ไม่คำนึงถึงขนาดตัวพิมพ์ ดังนั้นตัวพิมพ์ใหญ่และตัวพิมพ์เล็กจะถือว่าเหมือนกันเมื่อจับคู่ค่าข้อความ
- MATCH ส่งคืน N/A ด้วยเหตุผลหลายประการ: หาก match_type เป็น 0 และไม่พบ lookup_value หาก match_type เป็น -1 และ lookup_array ไม่เรียงลำดับจากมากไปน้อย หาก match_type คือ 1 และ lookup_array ไม่เรียงจากน้อยไปหามาก เรียงลำดับ และถ้า lookup_array ไม่ใช่แถวหรือคอลัมน์เดียว
- คุณสามารถใช้อักขระตัวแทนในอาร์กิวเมนต์ lookup_value หาก match_type เป็น 0 และ lookup_value เป็นสตริงข้อความ เครื่องหมายคำถามจะจับคู่อักขระตัวเดียวและเครื่องหมายดอกจันจะตรงกับลำดับของอักขระใดๆ (e.g., =MATCH("โจ", 1:1, 0)). หากต้องการใช้ MATCH เพื่อค้นหาเครื่องหมายคำถามหรือเครื่องหมายดอกจัน ให้พิมพ์ ~ ก่อน
- INDEX กลับมา REF! ถ้า row_num และ column_num ไม่ชี้ไปที่เซลล์ภายในอาร์เรย์
ฟังก์ชัน Excel ที่เกี่ยวข้อง
ฟังก์ชัน MATCH นั้นคล้ายกับ LOOKUP แต่ MATCH จะคืนค่าตำแหน่งของไอเท็มแทนที่จะเป็นตัวไอเท็มเอง
VLOOKUP เป็นอีกหนึ่งฟังก์ชันการค้นหาที่คุณสามารถใช้ได้ใน Excel แต่ไม่เหมือน MATCH ที่ต้องใช้ INDEX สำหรับการค้นหาขั้นสูง สูตร VLOOKUP ต้องการเพียงฟังก์ชันเดียวเท่านั้น
