ไปยังเนื้อหาหลัก

Excel INDEX MATCH: การค้นหาขั้นพื้นฐานและขั้นสูง

ผู้เขียน: อแมนดา ลี แก้ไขล่าสุด: 2024-12-12

ใน Excel การดึงข้อมูลเฉพาะอย่างแม่นยำมักมีความจำเป็นบ่อยครั้ง แม้ว่าฟังก์ชัน INDEX และ MATCH ต่างก็มีจุดแข็งของตัวเอง แต่เมื่อรวมเข้าด้วยกันจะปลดล็อกชุดเครื่องมืออันทรงพลังสำหรับการค้นหาข้อมูล ทั้งสองอย่างนี้ช่วยอำนวยความสะดวกในการค้นหาที่หลากหลาย ตั้งแต่การค้นหาแนวนอนและแนวตั้งขั้นพื้นฐาน ไปจนถึงฟังก์ชันการทำงานขั้นสูง เช่น การค้นหาแบบสองทาง คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ และการค้นหาหลายเกณฑ์ การจับคู่ INDEX และ MATCH นำเสนอความสามารถที่ได้รับการปรับปรุงเมื่อเทียบกับ VLOOKUP ทำให้มีตัวเลือกการค้นหาข้อมูลที่หลากหลายยิ่งขึ้น ในบทช่วยสอนนี้ เราจะมาเจาะลึกความเป็นไปได้ที่พวกเขาสามารถทำได้ร่วมกัน


วิธีใช้ INDEX และ MATCH ใน Excel

ก่อนที่เราจะใช้ฟังก์ชัน INDEX และ MATCH มาทำความเข้าใจกันก่อนว่า INDEX และ MATCH จะช่วยเราค้นหาค่าได้อย่างไรก่อน


วิธีใช้ฟังก์ชัน INDEX ใน Excel

เทศกาล ดัชนี ฟังก์ชันใน Excel ส่งคืนค่าที่ตำแหน่งที่กำหนดในช่วงที่ระบุ ไวยากรณ์ของฟังก์ชัน INDEX มีดังนี้:

=INDEX(array, row_num, [column_num])
  • แถว (จำเป็น) หมายถึงช่วงที่คุณต้องการคืนค่าจาก
  • row_num (จำเป็น เว้นแต่. คอลัมน์_num มีอยู่) หมายถึงหมายเลขแถวของอาร์เรย์
  • คอลัมน์_num (ไม่จำเป็น แต่จำเป็นหาก row_num ละไว้) หมายถึงหมายเลขคอลัมน์ของอาร์เรย์

ตัวอย่างเช่น ที่จะรู้ว่า คะแนนของเจฟฟ์ที่ 6นักเรียนคนที่อยู่ในรายชื่อ คุณสามารถใช้ฟังก์ชัน INDEX ได้ดังนี้:

=INDEX(C2:C11,6)

ภาพหน้าจอผลลัพธ์ของสูตร INDEX ที่ได้คะแนนของนักเรียนชั้น ป.6

√ หมายเหตุ: ช่วง C2: C11 คือที่ที่คะแนนระบุไว้ในขณะที่ตัวเลข 6 พบคะแนนสอบของ 6นักเรียนคนนั้น

มาทำแบบทดสอบกันสักหน่อย สำหรับสูตร =ดัชนี(A1:C1,2)แล้วมันจะกลับมามีค่าเท่าไร? --- ใช่มันจะกลับมา วันเกิดที่ 2ค่าลำดับในแถวที่กำหนด

ตอนนี้เราควรรู้ว่าฟังก์ชัน INDEX สามารถทำงานได้อย่างสมบูรณ์แบบกับช่วงแนวนอนหรือแนวตั้ง แต่ถ้าเราต้องการให้คืนค่าในช่วงที่มากขึ้นด้วยหลายแถวและคอลัมน์ ในกรณีนี้ เราควรใช้ทั้งหมายเลขแถวและหมายเลขคอลัมน์ ตัวอย่างเช่น เพื่อค้นหา คะแนนของเจฟฟ์ ภายในช่วงตารางแทนที่จะเป็นคอลัมน์เดียว เราสามารถหาคะแนนของเขาได้ด้วย a แถวที่ 6 และ คอลัมน์หมายเลข 3 ใน เซลล์ผ่าน A2 ถึง C11 อย่างนี้:

=INDEX(A2:C11,6,3)

ภาพหน้าจอของผลลัพธ์ของสูตร INDEX ที่ส่งคืนคะแนนของ Jeff จากช่วงตาราง

สิ่งที่เราควรรู้เกี่ยวกับฟังก์ชัน INDEX ใน Excel:
  • ฟังก์ชัน INDEX สามารถทำงานกับช่วงแนวตั้งและแนวนอนได้
  • ถ้าทั้งสองอย่าง row_num และ คอลัมน์_num มีการใช้ข้อโต้แย้ง row_num ไปข้างหน้าของ คอลัมน์_numและ INDEX จะดึงค่าที่จุดตัดของค่าที่ระบุ row_num และ คอลัมน์_num.

อย่างไรก็ตาม สำหรับฐานข้อมูลขนาดใหญ่ที่มีหลายแถวและหลายคอลัมน์ การใช้สูตรที่มีหมายเลขแถวและหมายเลขคอลัมน์ที่แน่นอนนั้นไม่สะดวกอย่างแน่นอน ดังนั้น เราจึงควรใช้ฟังก์ชัน MATCH ร่วมกัน


วิธีใช้ฟังก์ชัน MATCH ใน Excel

ฟังก์ชัน MATCH ใน Excel จะส่งกลับค่าตัวเลข ซึ่งเป็นตำแหน่งของรายการเฉพาะในช่วงที่กำหนด ไวยากรณ์ของฟังก์ชัน MATCH มีดังนี้:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value (จำเป็น) หมายถึงค่าที่จะจับคู่ใน lookup_array.
  • lookup_array (จำเป็น) หมายถึงช่วงของเซลล์ที่คุณต้องการให้ MATCH ค้นหา
  • match_type (ตัวเลือก): 1, 0 or -1.
    • 1 (ค่าเริ่มต้น) MATCH จะค้นหาค่าที่ใหญ่ที่สุดที่น้อยกว่าหรือเท่ากับ lookup_value. ค่าใน lookup_array ต้องเรียงลำดับจากน้อยไปมาก
    • 0, MATCH จะค้นหาค่าแรกที่เท่ากับ lookup_value. ค่าใน lookup_array สามารถอยู่ในลำดับใดก็ได้ (สำหรับกรณีที่ตั้งค่าประเภทการจับคู่เป็น 0 คุณสามารถใช้อักขระตัวแทนได้)
    • -1, MATCH จะค้นหาค่าที่น้อยที่สุดที่มากกว่าหรือเท่ากับ lookup_value. ค่าใน lookup_array ต้องเรียงจากมากไปน้อย

ตัวอย่างเช่น ที่จะรู้ว่า ตำแหน่งของ Vera ในรายชื่อคุณสามารถใช้สูตร MATCH ได้ดังนี้:

=MATCH("Vera",A2:A11,0)

ภาพหน้าจอแสดงผลลัพธ์ของสูตร MATCH ที่ส่งคืนตำแหน่งของ Vera ในรายการ

√ หมายเหตุ: ผลลัพธ์ “4” แสดงว่าชื่อ “เวร่า” อยู่ในอันดับที่ 4 ของรายการ

สิ่งที่เราควรรู้เกี่ยวกับฟังก์ชัน MATCH ใน Excel:
  • ฟังก์ชัน MATCH จะคืนค่าตำแหน่งของค่าการค้นหาในอาร์เรย์การค้นหา ไม่ใช่ค่าเอง
  • ฟังก์ชัน MATCH จะคืนค่าที่ตรงกันครั้งแรกในกรณีที่ซ้ำกัน
  • เช่นเดียวกับฟังก์ชัน INDEX ฟังก์ชัน MATCH สามารถทำงานกับช่วงแนวตั้งและแนวนอนได้เช่นกัน
  • MATCH ไม่คำนึงถึงขนาดตัวพิมพ์
  • ถ้า lookup_value ของสูตร MATCH จะอยู่ในรูปแบบข้อความใส่เครื่องหมายคำพูด
  • ถ้า lookup_value ไม่พบใน lookup_arrayที่ # N / A ข้อผิดพลาดถูกส่งกลับ

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


วิธีรวม INDEX และ MATCH ใน Excel

โปรดดูตัวอย่างด้านล่างเพื่อดูว่าเราจะรวมฟังก์ชัน INDEX และ MATCH เข้าด้วยกันได้อย่างไร:

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

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

ภาพหน้าจอแสดงสูตรและผลลัพธ์สำหรับคะแนนของเอเวลิน

เนื่องจากสูตรอาจดูซับซ้อนเล็กน้อย เรามาดูแต่ละส่วนกันดีกว่า

ภาพหน้าจอแสดงการแยกย่อยของสูตรสำหรับการรวม INDEX และ MATCH เพื่อหาคะแนนของเอเวลิน

เทศกาล ดัชนี สูตรประกอบด้วยสามอาร์กิวเมนต์:

  • row_num: MATCH("เอเวลิน",A2:A11,0) ให้ INDEX พร้อมตำแหน่งแถวของค่า "Evelyn"ในช่วง A2: A11ซึ่งเป็น 5.
  • คอลัมน์_num: 3 ระบุไฟล์ 3คอลัมน์ rd สำหรับ INDEX เพื่อค้นหาคะแนนภายในอาร์เรย์
  • แถว: A2: C11 สั่งให้ INDEX ส่งกลับค่าที่ตรงกันที่จุดตัดของแถวและคอลัมน์ที่ระบุ ภายในช่วงที่ขยายจาก A2 ถึง C11. ในที่สุดเราก็ได้ผลลัพธ์ 90.

ในสูตรข้างต้น เราใช้ค่าฮาร์ดโค้ด “เอเวลิน”. อย่างไรก็ตาม ในทางปฏิบัติ ค่าฮาร์ดโค้ดนั้นใช้ไม่ได้จริง เนื่องจากจะต้องมีการแก้ไขทุกครั้งที่เราค้นหาข้อมูลที่แตกต่างกัน เช่น คะแนนของนักเรียนคนอื่น ในสถานการณ์เช่นนี้ เราสามารถใช้การอ้างอิงเซลล์เพื่อสร้างสูตรแบบไดนามิกได้ เช่น ในกรณีนี้ ฉันจะทำ เปลี่ยน "เอเวลิน" เป็น F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

(AD) ทำให้การค้นหาง่ายขึ้นด้วย Kutools: ไม่ต้องพิมพ์สูตร!

Kutools สำหรับ Excel's การค้นหาขั้นสูง ให้ เครื่องมือค้นหาที่หลากหลาย ออกแบบมาเพื่อตอบสนองทุกความต้องการของคุณ ไม่ว่าคุณจะทำการค้นหาหลายเกณฑ์ ค้นหาในหลายแผ่นงาน หรือทำการค้นหาแบบหนึ่งต่อหลายรายการ การค้นหาขั้นสูง ลดความซับซ้อนของกระบวนการด้วยการคลิกเพียงไม่กี่ครั้ง สำรวจคุณสมบัติเหล่านี้ เพื่อดูว่า การค้นหาขั้นสูง เปลี่ยนวิธีที่คุณโต้ตอบกับข้อมูล Excel บอกลาความยุ่งยากในการจำสูตรที่ซับซ้อน

ภาพหน้าจอของเครื่องมือ Super Lookup ของ Kutools สำหรับ Excel ใน Ribbon ของ Excel

Kutools สำหรับ Excel - เพิ่มประสิทธิภาพ Excel ด้วยเครื่องมือที่จำเป็นกว่า 300 รายการ เพลิดเพลินกับฟีเจอร์ AI ฟรีถาวร! Get It Now


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

ในส่วนนี้ เราจะพูดถึงสถานการณ์ต่างๆ ในการใช้ฟังก์ชัน INDEX และ MATCH เพื่อตอบสนองความต้องการที่แตกต่างกัน


INDEX และ MATCH เพื่อใช้การค้นหาแบบสองทาง

ในตัวอย่างก่อนหน้านี้ เราทราบหมายเลขคอลัมน์และใช้สูตร MATCH เพื่อค้นหาหมายเลขแถว แต่ถ้าเราไม่แน่ใจเกี่ยวกับหมายเลขคอลัมน์ด้วยล่ะ?

ในกรณีเช่นนี้ เราสามารถดำเนินการค้นหาแบบสองทางหรือที่เรียกว่าการค้นหาเมทริกซ์ โดยใช้ฟังก์ชัน MATCH สองฟังก์ชัน: ฟังก์ชันหนึ่งเพื่อค้นหาหมายเลขแถว และอีกฟังก์ชันหนึ่งเพื่อกำหนดหมายเลขคอลัมน์ เช่น ต้องการทราบ คะแนนของเอเวลิน, เราควรใช้สูตร:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

ภาพหน้าจอแสดงการค้นหาสองทางโดยใช้ INDEX และ MATCH ใน Excel เพื่อค้นหาคะแนนของเอเวลิน

สูตรนี้ทำงานอย่างไร:
  • สูตร MATCH สูตรแรกค้นหาตำแหน่งของ Evelyn ในรายการ A2:A11 ที่ระบุ 5 เป็นหมายเลขแถวถึง INDEX
  • สูตร MATCH ที่สองจะกำหนดคอลัมน์สำหรับคะแนนและผลตอบแทน 3 เป็นหมายเลขคอลัมน์ถึง INDEX
  • สูตรลดความซับซ้อนของ =ดัชนี(A2:C11,5,3)และผลตอบแทน INDEX 90.

INDEX และ MATCH เพื่อใช้การค้นหาด้านซ้าย

ตอนนี้ เรามาพิจารณาสถานการณ์ที่คุณต้องกำหนดคลาสของ Evelyn กัน คุณอาจสังเกตเห็นว่าคอลัมน์คลาสอยู่ในตำแหน่งด้านซ้ายของคอลัมน์ชื่อ ซึ่งเป็นสถานการณ์ที่เกินความสามารถของฟังก์ชันการค้นหา Excel อันทรงพลังอื่น ๆ นั่นคือ VLOOKUP

ที่จริงแล้ว ความสามารถในการค้นหาทางด้านซ้ายเป็นแง่มุมหนึ่งที่การผสมผสานระหว่าง INDEX และ MATCH ช่วยให้โดดเด่นกว่า VLOOKUP

การค้นหา ชั้นของเอเวลินให้ใช้สูตรต่อไปนี้เพื่อ ค้นหา Evelyn ใน B2:B11 และ ดึงค่าที่สอดคล้องกันจาก A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

ภาพหน้าจอแสดงวิธีการใช้ INDEX และ MATCH เพื่อค้นหาคลาสของ Evelyn จากการค้นหาทางด้านซ้ายใน Excel

หมายเหตุ คุณสามารถค้นหาค่าเฉพาะทางซ้ายได้อย่างง่ายดายโดยใช้ ค้นหาจากขวาไปซ้าย คุณลักษณะของ Kutools สำหรับ Excel เพียงไม่กี่คลิก หากต้องการใช้คุณลักษณะนี้ ให้ไปที่ Kutools ใน Excel ของคุณ แล้วคลิก การค้นหาขั้นสูง > ค้นหาจากขวาไปซ้าย ใน สูตร กลุ่ม

ภาพหน้าจอของฟีเจอร์ LOOKUP จากขวาไปซ้ายใน Kutools สำหรับ Excel

Kutools สำหรับ Excel - เพิ่มประสิทธิภาพ Excel ด้วยเครื่องมือที่จำเป็นกว่า 300 รายการ เพลิดเพลินกับฟีเจอร์ AI ฟรีถาวร! Get It Now


INDEX และ MATCH เพื่อใช้การค้นหาแบบพิจารณาตัวพิมพ์เล็กและตัวพิมพ์ใหญ่

ฟังก์ชัน MATCH นั้นโดยเนื้อแท้แล้วจะไม่คำนึงถึงตัวพิมพ์เล็กหรือใหญ่ อย่างไรก็ตาม เมื่อคุณต้องการให้สูตรของคุณแยกความแตกต่างระหว่างอักขระตัวพิมพ์ใหญ่และตัวพิมพ์เล็ก คุณสามารถปรับปรุงสูตรได้โดยการผสาน EXACT การทำงาน. ด้วยการรวมฟังก์ชัน MATCH กับ EXACT ในสูตร INDEX คุณจะสามารถดำเนินการค้นหาแบบคำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ได้อย่างมีประสิทธิภาพ ดังที่แสดงด้านล่าง:

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))
  • แถว หมายถึงช่วงที่คุณต้องการส่งกลับค่า
  • lookup_value หมายถึงค่าที่จะจับคู่โดยคำนึงถึงกรณีของตัวอักษรใน lookup_array.
  • lookup_array หมายถึงช่วงของเซลล์ที่คุณต้องการให้ MATCH เปรียบเทียบด้วย lookup_value.

ตัวอย่างเช่น ที่จะรู้ว่า คะแนนสอบของจิมมี่, ใช้สูตรต่อไปนี้:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ หมายเหตุ: นี่คือสูตรอาร์เรย์ที่คุณต้องป้อนด้วย Ctrl + เปลี่ยน + เข้าสู่ยกเว้นใน Excel 365, Excel 2021 และเวอร์ชันใหม่กว่า

ภาพหน้าจอแสดงวิธีการใช้ INDEX และ MATCH กับ EXACT สำหรับการค้นหาแบบแยกแยะตัวพิมพ์ใหญ่-เล็กใน Excel

สูตรนี้ทำงานอย่างไร:
  • ฟังก์ชัน EXACT จะเปรียบเทียบ “จิมมี่” ด้วยค่าในรายการ A2: A11โดยพิจารณาจากกรณีของอักขระ: หากสตริงทั้งสองตรงกันอย่างแม่นยำ โดยพิจารณาทั้งอักขระตัวพิมพ์ใหญ่และตัวพิมพ์เล็ก ฟังก์ชัน EXACT จะส่งกลับ TRUE; มิฉะนั้นจะกลับมา FALSE. เป็นผลให้เราได้รับ อาร์เรย์ที่มีค่า TRUE และ FALSE.
  • จากนั้นฟังก์ชัน MATCH จะดึงข้อมูล ตำแหน่งของค่า TRUE แรก ในอาร์เรย์ซึ่งควรจะเป็น 10.
  • สุดท้าย INDEX จะดึงค่าที่ 10ตำแหน่งที่จัดทำโดย MATCH ในอาร์เรย์

หมายเหตุ:

  • อย่าลืมใส่สูตรให้ถูกต้องโดยการกด Ctrl + Shift + Enterเว้นแต่คุณจะใช้ 365 Excel, 2021 Excel or เวอร์ชั่นใหม่กว่าในกรณีนี้ เพียงกด เข้าสู่.
  • สูตรข้างต้นค้นหาภายในรายการเดียว C2: C11. หากคุณต้องการค้นหาภายในช่วงที่มีหลายคอลัมน์และแถว ให้พูด A2: C11คุณควรเสนอทั้งหมายเลขคอลัมน์และแถวให้กับ INDEX:
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • ในสูตรที่แก้ไขนี้ เราใช้ฟังก์ชัน MATCH เพื่อค้นหา "JIMMY" โดยพิจารณาตัวพิมพ์เล็กและใหญ่ในช่วง A2: A11และเมื่อเราพบรายการที่ตรงกัน เราจะดึงค่าที่สอดคล้องกันจาก 3คอลัมน์ rd ของช่วง A2: C11.

INDEX และ MATCH เพื่อค้นหาคู่ที่ใกล้เคียงที่สุด

ใน Excel คุณอาจพบสถานการณ์ที่คุณจำเป็นต้องค้นหาค่าที่ใกล้เคียงที่สุดหรือใกล้เคียงที่สุดกับค่าเฉพาะภายในชุดข้อมูล ในสถานการณ์เช่นนี้ การใช้ฟังก์ชัน INDEX และ MATCH ร่วมกัน พร้อมด้วยฟังก์ชัน ABS และ MIN จะมีประโยชน์อย่างเหลือเชื่อ

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • แถว หมายถึงช่วงที่คุณต้องการส่งกลับค่า
  • lookup_array หมายถึงช่วงค่าที่คุณต้องการค้นหาค่าที่ตรงกันที่สุด lookup_value.
  • lookup_value หมายถึงค่าเพื่อค้นหาการจับคู่ที่ใกล้เคียงที่สุด

ตัวอย่างเช่นหากต้องการทราบ ซึ่งมีคะแนนใกล้เคียงที่สุดถึง 85ให้ใช้สูตรต่อไปนี้เพื่อ ค้นหาคะแนนที่ใกล้เคียงที่สุดถึง 85 ใน C2:C11 และ ดึงค่าที่สอดคล้องกันจาก A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ หมายเหตุ: นี่คือสูตรอาร์เรย์ที่คุณต้องป้อนด้วย Ctrl + เปลี่ยน + เข้าสู่ยกเว้นใน Excel 365, Excel 2021 และเวอร์ชันใหม่กว่า

ภาพหน้าจอสาธิตวิธีการใช้ INDEX และ MATCH กับฟังก์ชัน ABS และ MIN เพื่อค้นหาการจับคู่ที่ใกล้เคียงที่สุดใน Excel

สูตรนี้ทำงานอย่างไร:
  • เอบีเอส(C2:C11-85) คำนวณผลต่างสัมบูรณ์ระหว่างแต่ละค่าในช่วง C2: C11 และ 85ส่งผลให้เกิดอาร์เรย์ของความแตกต่างที่แน่นอน
  • ต่ำสุด(เอบีเอส(C2:C11-85)) ค้นหาค่าต่ำสุดในอาร์เรย์ของผลต่างสัมบูรณ์ ซึ่งแสดงถึงผลต่างที่ใกล้เคียงที่สุดกับ 85
  • ฟังก์ชัน MATCH MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) แล้วหาตำแหน่งของผลต่างสัมบูรณ์ขั้นต่ำในอาร์เรย์ของผลต่างสัมบูรณ์ซึ่งควรจะเป็น 10.
  • สุดท้าย INDEX จะดึงค่าที่ตำแหน่งในรายการ A2: A11 ซึ่งสอดคล้องกับคะแนนที่ใกล้เคียงที่สุด 85 อยู่ในช่วง C2: C11.

หมายเหตุ:

  • อย่าลืมใส่สูตรให้ถูกต้องโดยการกด Ctrl + Shift + Enterเว้นแต่คุณจะใช้ 365 Excel, 2021 Excel or เวอร์ชั่นใหม่กว่าในกรณีนี้ เพียงกด เข้าสู่.
  • ในกรณีที่เสมอกัน สูตรนี้จะคืนค่านัดแรก
  • การค้นหา การแข่งขันที่ใกล้เคียงที่สุดกับคะแนนเฉลี่ยแทนที่ 85 ในสูตรด้วย ค่าเฉลี่ย(C2:C11).

INDEX และ MATCH เพื่อใช้การค้นหาที่มีหลายเกณฑ์

เมื่อต้องการค้นหาค่าที่ตรงตามเงื่อนไขหลายข้อ ซึ่งคุณต้องค้นหาในสองคอลัมน์ขึ้นไป ให้ใช้สูตรต่อไปนี้ สูตรช่วยให้คุณทำการค้นหาหลายเกณฑ์โดยการระบุเงื่อนไขต่างๆ ในคอลัมน์ต่างๆ ช่วยให้คุณค้นหาค่าที่ต้องการซึ่งตรงกับเกณฑ์ที่ระบุทั้งหมด

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…), 0))

√ หมายเหตุ: นี่คือสูตรอาร์เรย์ที่คุณต้องป้อนด้วย Ctrl + เปลี่ยน + เข้าสู่. วงเล็บปีกกาคู่หนึ่งจะปรากฏขึ้นในแถบสูตร

  • แถว หมายถึงช่วงที่คุณต้องการส่งกลับค่า
  • (lookup_value=lookup_array) แสดงถึงเงื่อนไขเดียว เงื่อนไขนี้จะตรวจสอบว่ามีรายการใดรายการหนึ่งหรือไม่ lookup_value ตรงกับค่าใน lookup_array.

ตัวอย่างเช่น ในการหา คะแนนของ Coco คลาส A ซึ่งมีวันเกิดคือ 7/2/2008คุณสามารถใช้สูตรต่อไปนี้:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

ภาพหน้าจอสาธิตการใช้ INDEX และ MATCH เพื่อค้นหาเกณฑ์หลายรายการใน Excel

หมายเหตุ:

  • ในสูตรนี้ เราหลีกเลี่ยงค่าฮาร์ดโค้ด ทำให้ง่ายต่อการรับคะแนนที่มีข้อมูลที่แตกต่างกันโดยการปรับเปลี่ยนค่าในเซลล์ G2, G3และ G4.
  • ควรใส่สูตรโดยการกด Ctrl + Shift + Enter ยกเว้นใน 365 Excel, 2021 Excel or เวอร์ชั่นใหม่กว่าที่คุณสามารถกดได้ง่ายๆ เข้าสู่.
    หากลืมใช้เป็นประจำ Ctrl + Shift + Enter หากต้องการกรอกสูตรให้สมบูรณ์และได้รับผลลัพธ์ที่ไม่ถูกต้อง ให้ใช้สูตรที่ซับซ้อนกว่านี้เล็กน้อยต่อไปนี้ ซึ่งคุณสามารถกรอกสูตรง่ายๆ ได้ เข้าสู่ สำคัญ:
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • สูตรอาจซับซ้อนและท้าทายในการจดจำ หากต้องการทำให้การค้นหาหลายเกณฑ์ง่ายขึ้นโดยไม่จำเป็นต้องป้อนสูตรด้วยตนเอง ให้พิจารณาใช้ Kutools สำหรับ Excel's การค้นหาแบบหลายเงื่อนไข คุณสมบัติ. เมื่อคุณติดตั้ง Kutools แล้ว ให้ไปที่ Kutools ใน Excel ของคุณ แล้วคลิก การค้นหาขั้นสูง > การค้นหาแบบหลายเงื่อนไข ใน สูตร กลุ่ม

    ภาพหน้าจอของฟีเจอร์การค้นหาเงื่อนไขหลายรายการใน Kutools สำหรับ Excel

    Kutools สำหรับ Excel - เพิ่มประสิทธิภาพ Excel ด้วยเครื่องมือที่จำเป็นกว่า 300 รายการ เพลิดเพลินกับฟีเจอร์ AI ฟรีถาวร! Get It Now


INDEX และ MATCH เพื่อใช้การค้นหาในหลายคอลัมน์

ลองนึกภาพสถานการณ์ที่คุณกำลังจัดการกับคอลัมน์ข้อมูลหลายคอลัมน์ คอลัมน์แรกทำหน้าที่เป็นคีย์ในการจำแนกข้อมูลในคอลัมน์อื่นๆ ในการกำหนดหมวดหมู่หรือการจัดหมวดหมู่สำหรับรายการใดรายการหนึ่ง คุณจะต้องทำการค้นหาในคอลัมน์ข้อมูลและเชื่อมโยงกับคีย์ที่เกี่ยวข้องในคอลัมน์อ้างอิง

ตัวอย่างเช่น ในตารางด้านล่าง เราจะจับคู่ Shawn นักเรียนกับชั้นเรียนที่เกี่ยวข้องโดยใช้ INDEX และ MATCH ได้อย่างไร คุณสามารถทำได้โดยใช้สูตร แต่สูตรนั้นค่อนข้างกว้างขวางและอาจเป็นเรื่องยากที่จะเข้าใจ ไม่ต้องพูดถึงการจำและพิมพ์

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

ภาพหน้าจอของสูตรที่ใช้ค้นหาข้อมูลในหลายคอลัมน์

นั่นคือสิ่งที่ Kutools สำหรับ Excel's จัดทำดัชนีและจับคู่ในหลายคอลัมน์ คุณลักษณะนี้มีประโยชน์ ช่วยให้กระบวนการง่ายขึ้น ทำให้ง่ายและรวดเร็วในการจับคู่รายการเฉพาะกับหมวดหมู่ที่เกี่ยวข้อง เพื่อปลดล็อกเครื่องมืออันทรงพลังนี้และจับคู่ Shawn กับคลาสของเขาได้อย่างง่ายดาย ดาวน์โหลดและติดตั้ง Kutools สำหรับ Add-in ของ Excelแล้วทำดังนี้:

  1. เลือกเซลล์ปลายทางที่คุณต้องการแสดงคลาสที่ตรงกัน
  2. เกี่ยวกับ Kutools คลิกแท็บ ตัวช่วยสูตร > ค้นหาและอ้างอิง > จัดทำดัชนีและจับคู่ในหลายคอลัมน์.
  3. ภาพหน้าจอของตัวเลือกดัชนีและการจับคู่ในหลายคอลัมน์บนแท็บ Kutools ใน Excel
  4. ในกล่องโต้ตอบป๊อปอัป ให้ทำดังนี้:
    1. คลิกที่ 1 ภาพหน้าจอของปุ่มเลือกช่วงในกล่องโต้ตอบตัวช่วย Fomrulas ถัดจาก lookup_col เพื่อเลือกคอลัมน์ที่มีข้อมูลสำคัญที่คุณต้องการส่งคืน เช่น ชื่อคลาส (คุณสามารถเลือกได้เพียงคอลัมน์เดียวที่นี่)
    2. คลิกที่ 2 ภาพหน้าจอของปุ่มเลือกช่วงในกล่องโต้ตอบตัวช่วย Fomrulas ถัดจาก ตาราง_rng เพื่อเลือกเซลล์ให้ตรงกับค่าในเซลล์ที่เลือก lookup_colเช่น ชื่อนักเรียน
    3. คลิกที่ 3 ภาพหน้าจอของปุ่มเลือกช่วงในกล่องโต้ตอบตัวช่วย Fomrulas ถัดจาก lookup_value เพื่อเลือกเซลล์ที่มีชื่อนักเรียนที่คุณต้องการจับคู่กับชั้นเรียน ในกรณีนี้คือ Shawn
    4. คลิก OK.
    5. ภาพหน้าจอของกล่องโต้ตอบตัวช่วย Fomrulas

ผล

Kutools ได้สร้างสูตรโดยอัตโนมัติ และคุณจะเห็นชื่อคลาสของ Shawn ปรากฏในเซลล์ปลายทางทันที

ภาพหน้าจอของสูตรที่สร้างโดย Kutools เพื่อค้นหาชื่อคลาสของ Shawn จากตาราง

หมายเหตุ เพื่อทดลองใช้งาน จัดทำดัชนีและจับคู่ในหลายคอลัมน์ คุณจะต้องติดตั้ง Kutools for Excel บนคอมพิวเตอร์ของคุณ ใครยังไม่ได้ติดตั้งอย่ารอช้า --- ดาวน์โหลดและติดตั้งทันที. ทำให้ Excel ทำงานได้อย่างชาญฉลาดยิ่งขึ้นวันนี้!


INDEX และ MATCH เพื่อค้นหาค่าแรกที่ไม่ว่างเปล่า

หากต้องการดึงค่าแรกที่ไม่ใช่ค่าว่าง โดยไม่สนใจข้อผิดพลาด จากคอลัมน์หรือแถว คุณสามารถใช้สูตรตามฟังก์ชัน INDEX และ MATCH อย่างไรก็ตาม หากคุณไม่ต้องการละเว้นข้อผิดพลาดจากช่วงของคุณ ให้เพิ่มฟังก์ชัน ISBLANK

  • รับค่าที่ไม่ว่างค่าแรกในคอลัมน์หรือแถวโดยไม่สนใจข้อผิดพลาด:
  • =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))
  • รับค่าที่ไม่ว่างค่าแรกในคอลัมน์หรือแถวที่มีข้อผิดพลาด:
  • =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

    ภาพหน้าจอของสูตร INDEX MATCH ที่ใช้ค้นหาค่าที่ไม่ว่างแรก

หมายเหตุ:


INDEX และ MATCH เพื่อค้นหาค่าตัวเลขแรก

หากต้องการดึงค่าตัวเลขแรกจากคอลัมน์หรือแถว ให้ใช้สูตรตามฟังก์ชัน INDEX, MATCH และ ISNUMBER

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

ภาพหน้าจอของสูตร INDEX MATCH ที่ใช้ค้นหาค่าตัวเลขแรก

หมายเหตุ:

  • นี่คือสูตรอาร์เรย์ที่คุณต้องป้อนด้วย Ctrl + เปลี่ยน + เข้าสู่ยกเว้นใน Excel 365, Excel 2021 และเวอร์ชันใหม่กว่า
  • ดูบทช่วยสอนนี้สำหรับคำอธิบายโดยละเอียด: รับค่าตัวเลขแรกในคอลัมน์หรือแถว.

INDEX และ MATCH เพื่อค้นหาการเชื่อมโยง MAX หรือ MIN

หากคุณต้องการดึงค่าที่เกี่ยวข้องกับค่าสูงสุดหรือต่ำสุดภายในช่วง คุณสามารถใช้ฟังก์ชัน MAX หรือ MIN ร่วมกับฟังก์ชัน INDEX และ MATCH ได้

  • INDEX และ MATCH เพื่อดึงค่าที่เกี่ยวข้องกับค่าสูงสุด:
  • =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
  • INDEX และ MATCH เพื่อดึงค่าที่เกี่ยวข้องกับค่าต่ำสุด:
  • =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
  • มีสองอาร์กิวเมนต์ในสูตรข้างต้น:
    • แถว หมายถึงช่วงที่คุณต้องการส่งคืนข้อมูลที่เกี่ยวข้อง
    • lookup_array หมายถึง ชุดของค่าที่จะตรวจสอบหรือค้นหาเกณฑ์เฉพาะ เช่น ค่าสูงสุดหรือค่าต่ำสุด

เช่น หากคุณต้องการกำหนด ผู้ที่มีคะแนนสูงสุดให้ใช้สูตรต่อไปนี้:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

ภาพหน้าจอของสูตร INDEX MATCH ที่ใช้ค้นหาความสัมพันธ์ MAX

สูตรนี้ทำงานอย่างไร:
  • สูงสุด(C2:C11) ค้นหาค่าสูงสุดในช่วง C2: C11ซึ่งเป็น 96.
  • จากนั้นฟังก์ชัน MATCH จะค้นหาตำแหน่งของค่าสูงสุดในอาร์เรย์ C2: C11ซึ่งควรจะเป็น 1.
  • ในที่สุด INDEX จะดึงข้อมูล 1ค่า st ในรายการ A2: A11.

หมายเหตุ:

  • ในกรณีที่มีค่าสูงสุดหรือต่ำสุดมากกว่าหนึ่งค่า ดังที่เห็นในตัวอย่างด้านบน โดยที่นักเรียนสองคนได้คะแนนสูงสุดเท่ากัน สูตรนี้จะส่งคืนค่าที่ตรงกันรายการแรก
  • ในการพิจารณาว่าใครมีคะแนนต่ำสุด ให้ใช้สูตรต่อไปนี้:
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))

เคล็ดลับ: ปรับแต่งข้อความแสดงข้อผิดพลาด #N/A ของคุณเอง

เมื่อทำงานกับฟังก์ชัน INDEX และ MATCH ของ Excel คุณอาจพบข้อผิดพลาด #N/A เมื่อไม่มีผลลัพธ์ที่ตรงกัน ตัวอย่างเช่น ในตารางด้านล่าง เมื่อพยายามค้นหาคะแนนของนักเรียนชื่อ Samantha ข้อผิดพลาด #N/A จะปรากฏขึ้นเนื่องจากไม่มีอยู่ในชุดข้อมูล

ภาพหน้าจอของผลลัพธ์ข้อผิดพลาด #N/A ที่ส่งคืนโดยสูตร INDEX MATCH

เพื่อให้สเปรดชีตของคุณใช้งานง่ายยิ่งขึ้น คุณสามารถปรับแต่งข้อความแสดงข้อผิดพลาดนี้ได้โดยล้อมสูตร INDEX MATCH ไว้ในฟังก์ชัน IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

ภาพหน้าจอของข้อผิดพลาด #N/A ถูกแทนที่ด้วยข้อความที่ปรับแต่งโดยใช้ INDEX และ MATCH

หมายเหตุ:

  • คุณสามารถปรับแต่งข้อความแสดงข้อผิดพลาดของคุณได้โดยการแทนที่ "ไม่พบ" พร้อมข้อความที่คุณเลือก
  • หากคุณต้องการจัดการกับข้อผิดพลาดทั้งหมด ไม่ใช่แค่ #N/A ให้พิจารณาใช้ การอ้างอิง ทำหน้าที่แทน ไอเอฟเอ็นเอ:
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    โปรดทราบว่าอาจไม่แนะนำให้ระงับข้อผิดพลาดทั้งหมด เนื่องจากข้อผิดพลาดดังกล่าวทำหน้าที่เป็นการแจ้งเตือนปัญหาที่อาจเกิดขึ้นในสูตรของคุณ

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