ข้อควรรู้
- สามารถใช้ฟังก์ชัน INDEX เพียงอย่างเดียวได้ แต่การซ้อนฟังก์ชัน MATCH ไว้ภายในจะสร้างการค้นหาขั้นสูง
- ฟังก์ชันที่ซ้อนกันนี้มีความยืดหยุ่นมากกว่า VLOOKUP และสามารถให้ผลลัพธ์ได้เร็วกว่า
บทความนี้อธิบายวิธีใช้ฟังก์ชัน INDEX และ MATCH ร่วมกันใน Excel ทุกเวอร์ชัน รวมถึง Excel 2019 และ Microsoft 365
ฟังก์ชัน INDEX และ MATCH คืออะไร?
INDEX และ MATCH เป็นฟังก์ชันการค้นหาของ Excel แม้ว่าจะเป็นฟังก์ชัน 2 ฟังก์ชันที่แยกจากกันโดยสิ้นเชิง แต่ก็สามารถนำมารวมกันเพื่อสร้างสูตรขั้นสูงได้
ฟังก์ชัน INDEX ส่งคืนค่าหรือการอ้างอิงไปยังค่าจากภายในการเลือกเฉพาะ ตัวอย่างเช่น สามารถใช้เพื่อค้นหาค่าในแถวที่สองของชุดข้อมูล หรือในแถวที่ห้าและคอลัมน์ที่สาม
แม้ว่า INDEX จะสามารถใช้ได้เพียงลำพัง แต่การซ้อน MATCH ในสูตรจะทำให้มีประโยชน์มากขึ้นอีกเล็กน้อย ฟังก์ชัน MATCH จะค้นหารายการที่ระบุในช่วงของเซลล์ แล้วส่งกลับตำแหน่งสัมพัทธ์ของรายการในช่วง ตัวอย่างเช่น สามารถใช้เพื่อกำหนดว่าชื่อใดชื่อหนึ่งเป็นรายการที่สามในรายชื่อ
INDEX และ MATCH ไวยากรณ์และอาร์กิวเมนต์
นี่เป็นวิธีที่ต้องเขียนฟังก์ชันทั้งสองเพื่อให้ Excel เข้าใจ:
=ดัชนี (อาร์เรย์ , row_num , [column_num ])
- อาร์เรย์ คือช่วงของเซลล์ที่สูตรจะใช้ อาจเป็นแถวและคอลัมน์ได้ตั้งแต่หนึ่งแถวขึ้นไป เช่น 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 เป็นค่าเริ่มต้นหากละเว้นอาร์กิวเมนต์นี้
ใช้ 1 หรือ -1 สำหรับเวลาที่คุณต้องการเรียกใช้การค้นหาโดยประมาณตามมาตราส่วน เช่น เมื่อต้องจัดการกับตัวเลขและเมื่อการประมาณนั้นใช้ได้ แต่จำไว้ว่าถ้าคุณไม่ระบุ match_type , 1 จะเป็นค่าเริ่มต้น ซึ่งอาจบิดเบือนผลลัพธ์หากคุณต้องการการจับคู่แบบตรงทั้งหมด
ตัวอย่าง INDEX และ MATCH สูตร
ก่อนที่เราจะดูวิธีรวม INDEX และ MATCH ให้เป็นสูตรเดียว เราต้องเข้าใจว่าฟังก์ชันเหล่านี้ทำงานอย่างไร
ตัวอย่างดัชนี
=INDEX(A1:B2,2,2)
=INDEX(A1:B1,1)
=INDEX(2:2,1)
=INDEX(B1:B2,1)
ในตัวอย่างนี้แรก มีสี่สูตร 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("Stacy",A2:D2,0)
=MATCH(14,D1:D2)
=MATCH(14,D1:D2,-1)
=MATCH(13,A1:D1,0)
ต่อไปนี้คือตัวอย่างง่ายๆ สี่ตัวอย่างของฟังก์ชัน MATCH:
- =MATCH("สเตซี่",A2:D2,0) กำลังค้นหา สเตซี่ ในช่วง 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 อาร์กิวเมนต์ if match_type คือ 0 และ lookup_value เป็นสตริงข้อความ เครื่องหมายคำถามจะจับคู่อักขระตัวเดียวและเครื่องหมายดอกจันจะตรงกับลำดับของอักขระใดๆ (เช่น =MATCH("Jo*",1:1,0) ). หากต้องการใช้ MATCH เพื่อค้นหาเครื่องหมายคำถามหรือเครื่องหมายดอกจัน ให้พิมพ์ ~ ก่อน
- INDEX คืนค่า #REF! ถ้า row_num และ column_num อย่าชี้ไปที่เซลล์ภายในอาร์เรย์
ฟังก์ชัน Excel ที่เกี่ยวข้อง
ฟังก์ชัน MATCH คล้ายกับ LOOKUP แต่ MATCH จะคืนค่า ตำแหน่ง ของสินค้าแทนตัวสินค้าเอง
VLOOKUP เป็นฟังก์ชันการค้นหาอื่นที่คุณสามารถใช้ได้ใน Excel แต่ไม่เหมือน MATCH ซึ่งต้องใช้ INDEX สำหรับการค้นหาขั้นสูง สูตร VLOOKUP ต้องการเพียงฟังก์ชันเดียวเท่านั้น
