Excel INDEX MATCH: การค้นหาขั้นพื้นฐานและขั้นสูง
ใน Excel การดึงข้อมูลเฉพาะอย่างแม่นยำมักมีความจำเป็นบ่อยครั้ง แม้ว่าฟังก์ชัน INDEX และ MATCH ต่างก็มีจุดแข็งของตัวเอง แต่เมื่อรวมเข้าด้วยกันจะปลดล็อกชุดเครื่องมืออันทรงพลังสำหรับการค้นหาข้อมูล ทั้งสองอย่างนี้ช่วยอำนวยความสะดวกในการค้นหาที่หลากหลาย ตั้งแต่การค้นหาแนวนอนและแนวตั้งขั้นพื้นฐาน ไปจนถึงฟังก์ชันการทำงานขั้นสูง เช่น การค้นหาแบบสองทาง คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ และการค้นหาหลายเกณฑ์ การจับคู่ INDEX และ MATCH นำเสนอความสามารถที่ได้รับการปรับปรุงเมื่อเทียบกับ VLOOKUP ทำให้มีตัวเลือกการค้นหาข้อมูลที่หลากหลายยิ่งขึ้น ในบทช่วยสอนนี้ เราจะมาเจาะลึกความเป็นไปได้ที่พวกเขาสามารถทำได้ร่วมกัน
วิธีใช้ INDEX และ MATCH ใน Excel
ก่อนที่เราจะใช้ฟังก์ชัน INDEX และ MATCH มาทำความเข้าใจกันก่อนว่า INDEX และ MATCH จะช่วยเราค้นหาค่าได้อย่างไรก่อน
วิธีใช้ฟังก์ชัน INDEX ใน Excel
พื้นที่ ดัชนี ฟังก์ชันใน Excel ส่งคืนค่าที่ตำแหน่งที่กำหนดในช่วงที่ระบุ ไวยากรณ์ของฟังก์ชัน INDEX มีดังนี้:
- แถว (จำเป็น) หมายถึงช่วงที่คุณต้องการคืนค่าจาก
- row_num (จำเป็น เว้นแต่. คอลัมน์_num มีอยู่) หมายถึงหมายเลขแถวของอาร์เรย์
- คอลัมน์_num (ไม่จำเป็น แต่จำเป็นหาก row_num ละไว้) หมายถึงหมายเลขคอลัมน์ของอาร์เรย์
ตัวอย่างเช่น ที่จะรู้ว่า คะแนนของเจฟฟ์ที่ 6นักเรียนคนที่อยู่ในรายชื่อ คุณสามารถใช้ฟังก์ชัน INDEX ได้ดังนี้:
=INDEX(C2:C11,6)
√ หมายเหตุ: ช่วง C2: C11 คือที่ที่คะแนนระบุไว้ในขณะที่ตัวเลข 6 พบคะแนนสอบของ 6นักเรียนคนนั้น
มาทำแบบทดสอบกันสักหน่อย สำหรับสูตร =ดัชนี(A1:C1,2)แล้วมันจะกลับมามีค่าเท่าไร? --- ใช่มันจะกลับมา วันเกิดที่ 2ค่าลำดับในแถวที่กำหนด
ตอนนี้เราควรรู้ว่าฟังก์ชัน INDEX สามารถทำงานได้อย่างสมบูรณ์แบบกับช่วงแนวนอนหรือแนวตั้ง แต่ถ้าเราต้องการให้คืนค่าในช่วงที่มากขึ้นด้วยหลายแถวและคอลัมน์ ในกรณีนี้ เราควรใช้ทั้งหมายเลขแถวและหมายเลขคอลัมน์ ตัวอย่างเช่น เพื่อค้นหา คะแนนของเจฟฟ์ ภายในช่วงตารางแทนที่จะเป็นคอลัมน์เดียว เราสามารถหาคะแนนของเขาได้ด้วย a แถวที่ 6 และ คอลัมน์หมายเลข 3 ใน เซลล์ผ่าน A2 ถึง C11 อย่างนี้:
=INDEX(A2:C11,6,3)
- ฟังก์ชัน INDEX สามารถทำงานกับช่วงแนวตั้งและแนวนอนได้
- ถ้าทั้งสองอย่าง row_num และ คอลัมน์_num มีการใช้ข้อโต้แย้ง row_num ไปข้างหน้าของ คอลัมน์_numและ INDEX จะดึงค่าที่จุดตัดของค่าที่ระบุ row_num และ คอลัมน์_num.
อย่างไรก็ตาม สำหรับฐานข้อมูลขนาดใหญ่ที่มีหลายแถวและหลายคอลัมน์ ไม่สะดวกที่เราจะใช้สูตรที่มีหมายเลขแถวและคอลัมน์ที่แน่นอน และนี่คือตอนที่เราควรรวมการใช้ฟังก์ชัน MATCH เข้าด้วยกัน
วิธีใช้ฟังก์ชัน MATCH ใน Excel
ฟังก์ชัน MATCH ใน Excel จะส่งกลับค่าตัวเลข ซึ่งเป็นตำแหน่งของรายการเฉพาะในช่วงที่กำหนด ไวยากรณ์ของฟังก์ชัน MATCH มีดังนี้:
- 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)
√ หมายเหตุ: ผลลัพธ์ “4” แสดงว่าชื่อ “เวร่า” อยู่ในอันดับที่ 4 ของรายการ
- ฟังก์ชัน 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)
เนื่องจากสูตรอาจดูซับซ้อนเล็กน้อย เรามาดูแต่ละส่วนกันดีกว่า
พื้นที่ ดัชนี สูตรประกอบด้วยสามอาร์กิวเมนต์:
- 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 บอกลาความยุ่งยากในการจำสูตรที่ซับซ้อน
Kutools สำหรับ Excel - เสริมพลังให้กับคุณด้วยฟังก์ชันที่มีประโยชน์มากกว่า 300 รายการเพื่อประสิทธิภาพการทำงานที่ง่ายดาย อย่าพลาดโอกาสทดลองใช้งานฟีเจอร์เต็มรูปแบบฟรี 30 วัน! เริ่มตอนนี้เลย!
ตัวอย่างสูตร INDEX และ MATCH
ในส่วนนี้ เราจะพูดถึงสถานการณ์ต่างๆ ในการใช้ฟังก์ชัน INDEX และ MATCH เพื่อตอบสนองความต้องการที่แตกต่างกัน
INDEX และ MATCH เพื่อใช้การค้นหาแบบสองทาง
ในตัวอย่างก่อนหน้านี้ เราทราบหมายเลขคอลัมน์และใช้สูตร MATCH เพื่อค้นหาหมายเลขแถว แต่ถ้าเราไม่แน่ใจเกี่ยวกับหมายเลขคอลัมน์ด้วยล่ะ?
ในกรณีเช่นนี้ เราสามารถดำเนินการค้นหาแบบสองทางหรือที่เรียกว่าการค้นหาเมทริกซ์ โดยใช้ฟังก์ชัน MATCH สองฟังก์ชัน: ฟังก์ชันหนึ่งเพื่อค้นหาหมายเลขแถว และอีกฟังก์ชันหนึ่งเพื่อกำหนดหมายเลขคอลัมน์ เช่น ต้องการทราบ คะแนนของเอเวลิน, เราควรใช้สูตร:
=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))
- สูตร 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))
หมายเหตุ คุณสามารถค้นหาค่าเฉพาะทางซ้ายได้อย่างง่ายดายโดยใช้ ค้นหาจากขวาไปซ้าย คุณลักษณะของ Kutools สำหรับ Excel เพียงไม่กี่คลิก หากต้องการใช้คุณลักษณะนี้ ให้ไปที่ Kutools ใน Excel ของคุณ แล้วคลิก การค้นหาขั้นสูง > ค้นหาจากขวาไปซ้าย ใน สูตร กลุ่ม
หากคุณยังไม่ได้ติดตั้ง Kutools คลิกที่นี่เพื่อ ดาวน์โหลดและทดลองใช้ฟีเจอร์เต็มรูปแบบฟรี 30 วัน!
INDEX และ MATCH เพื่อใช้การค้นหาแบบพิจารณาตัวพิมพ์เล็กและตัวพิมพ์ใหญ่
ฟังก์ชัน MATCH คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ แต่เมื่อคุณต้องการให้สูตรของคุณแยกความแตกต่างระหว่างอักขระตัวพิมพ์ใหญ่และตัวพิมพ์เล็ก คุณสามารถปรับปรุงสูตรได้โดยการรวม EXACT การทำงาน. ด้วยการรวมฟังก์ชัน MATCH กับ EXACT ในสูตร INDEX คุณจะสามารถดำเนินการค้นหาแบบคำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ได้อย่างมีประสิทธิภาพ ดังที่แสดงด้านล่าง:
- แถว หมายถึงช่วงที่คุณต้องการส่งกลับค่า
- lookup_value หมายถึงค่าที่จะจับคู่โดยคำนึงถึงกรณีของตัวอักษรใน lookup_array.
- lookup_array หมายถึงช่วงของเซลล์ที่คุณต้องการให้ MATCH เปรียบเทียบด้วย lookup_value.
ตัวอย่างเช่น ที่จะรู้ว่า คะแนนสอบของจิมมี่, ใช้สูตรต่อไปนี้:
=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))
√ หมายเหตุ: นี่คือสูตรอาร์เรย์ที่คุณต้องป้อนด้วย Ctrl + เปลี่ยน + เข้าสู่ยกเว้นใน Excel 365 และ Excel 2021
- ฟังก์ชัน EXACT จะเปรียบเทียบ “จิมมี่” ด้วยค่าในรายการ A2: A11โดยพิจารณาจากกรณีของอักขระ: หากสตริงทั้งสองตรงกันอย่างแม่นยำ โดยพิจารณาทั้งอักขระตัวพิมพ์ใหญ่และตัวพิมพ์เล็ก ฟังก์ชัน EXACT จะส่งกลับ TRUE; มิฉะนั้นจะกลับมา FALSE. เป็นผลให้เราได้รับ อาร์เรย์ที่มีค่า TRUE และ FALSE.
- จากนั้นฟังก์ชัน MATCH จะดึงข้อมูล ตำแหน่งของค่า TRUE แรก ในอาร์เรย์ซึ่งควรจะเป็น 10.
- สุดท้าย INDEX จะดึงค่าที่ 10ตำแหน่งที่จัดทำโดย MATCH ในอาร์เรย์
หมายเหตุ:
- อย่าลืมใส่สูตรให้ถูกต้องโดยการกด Ctrl + Shift + Enterเว้นแต่คุณจะใช้ 365 Excel or 2021 Excelในกรณีนี้ เพียงกด เข้าสู่.
- สูตรข้างต้นค้นหาภายในรายการเดียว 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 จะมีประโยชน์อย่างเหลือเชื่อ
- แถว หมายถึงช่วงที่คุณต้องการส่งกลับค่า
- 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
- เอบีเอส(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 or 2021 Excelในกรณีนี้ เพียงกด เข้าสู่.
- ในกรณีที่เสมอกัน สูตรนี้จะคืนค่านัดแรก
- การค้นหา การแข่งขันที่ใกล้เคียงที่สุดกับคะแนนเฉลี่ยแทนที่ 85 ในสูตรด้วย ค่าเฉลี่ย(C2:C11).
INDEX และ MATCH เพื่อใช้การค้นหาที่มีหลายเกณฑ์
เมื่อต้องการค้นหาค่าที่ตรงตามเงื่อนไขหลายข้อ ซึ่งคุณต้องค้นหาในสองคอลัมน์ขึ้นไป ให้ใช้สูตรต่อไปนี้ สูตรช่วยให้คุณทำการค้นหาหลายเกณฑ์โดยการระบุเงื่อนไขต่างๆ ในคอลัมน์ต่างๆ ช่วยให้คุณค้นหาค่าที่ต้องการซึ่งตรงกับเกณฑ์ที่ระบุทั้งหมด
√ หมายเหตุ: นี่คือสูตรอาร์เรย์ที่คุณต้องป้อนด้วย 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))
หมายเหตุ:
- ในสูตรนี้ เราหลีกเลี่ยงค่าฮาร์ดโค้ด ทำให้ง่ายต่อการรับคะแนนที่มีข้อมูลที่แตกต่างกันโดยการปรับเปลี่ยนค่าในเซลล์ G2, G3และ G4.
- ควรใส่สูตรโดยการกด Ctrl + Shift + Enter ยกเว้นใน 365 Excel or 2021 Excelที่คุณสามารถกดได้ง่ายๆ เข้าสู่.
หากลืมใช้เป็นประจำ 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 คลิกที่นี่เพื่อ ดาวน์โหลดและทดลองใช้ฟีเจอร์เต็มรูปแบบฟรี 30 วัน!
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แล้วทำดังนี้:
- เลือกเซลล์ปลายทางที่คุณต้องการแสดงคลาสที่ตรงกัน
- เกี่ยวกับ Kutools คลิกแท็บ ตัวช่วยสูตร > ค้นหาและอ้างอิง > จัดทำดัชนีและจับคู่ในหลายคอลัมน์.
- ในกล่องโต้ตอบป๊อปอัป ให้ทำดังนี้:
- คลิกที่ 1 ถัดจาก lookup_col เพื่อเลือกคอลัมน์ที่มีข้อมูลสำคัญที่คุณต้องการส่งคืน เช่น ชื่อคลาส (คุณสามารถเลือกได้เพียงคอลัมน์เดียวที่นี่)
- คลิกที่ 2 ถัดจาก ตาราง_rng เพื่อเลือกเซลล์ให้ตรงกับค่าในเซลล์ที่เลือก lookup_colเช่น ชื่อนักเรียน
- คลิกที่ 3 ถัดจาก lookup_value เพื่อเลือกเซลล์ที่มีชื่อนักเรียนที่คุณต้องการจับคู่กับชั้นเรียน ในกรณีนี้คือ Shawn
- คลิก OK.
ผล
Kutools ได้สร้างสูตรโดยอัตโนมัติ และคุณจะเห็นชื่อคลาสของ Shawn ปรากฏในเซลล์ปลายทางทันที
หมายเหตุ เพื่อทดลองใช้งาน จัดทำดัชนีและจับคู่ในหลายคอลัมน์ คุณจะต้องติดตั้ง Kutools for Excel บนคอมพิวเตอร์ของคุณ ใครยังไม่ได้ติดตั้งอย่ารอช้า --- ดาวน์โหลดและติดตั้งทันทีเพื่อทดลองใช้ฟรี 30 วันโดยไม่มีข้อจำกัด. ทำให้ 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))
หมายเหตุ:
- ข้างต้นเป็นสูตรอาร์เรย์ที่ต้องให้คุณป้อนด้วย Ctrl + เปลี่ยน + เข้าสู่ยกเว้นใน Excel 365 และ Excel 2021
- ดูบทช่วยสอนนี้สำหรับคำอธิบายโดยละเอียด: รับค่าที่ไม่เว้นว่างแรกในคอลัมน์หรือแถว.
INDEX และ MATCH เพื่อค้นหาค่าตัวเลขแรก
หากต้องการดึงค่าตัวเลขแรกจากคอลัมน์หรือแถว ให้ใช้สูตรตามฟังก์ชัน INDEX, MATCH และ ISNUMBER
=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))
หมายเหตุ:
- นี่คือสูตรอาร์เรย์ที่คุณต้องป้อนด้วย 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))
- สูงสุด(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 จะปรากฏขึ้นเนื่องจากไม่มีอยู่ในชุดข้อมูล
เพื่อให้สเปรดชีตของคุณใช้งานง่ายยิ่งขึ้น คุณสามารถปรับแต่งข้อความแสดงข้อผิดพลาดนี้ได้โดยการล้อมสูตร INDEX MATCH ไว้ในฟังก์ชัน IFNA:
=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")
หมายเหตุ:
- คุณสามารถปรับแต่งข้อความแสดงข้อผิดพลาดของคุณได้โดยการแทนที่ "ไม่พบ" พร้อมข้อความที่คุณเลือก
- หากคุณต้องการจัดการกับข้อผิดพลาดทั้งหมด ไม่ใช่แค่ #N/A ให้พิจารณาใช้ การอ้างอิง ทำหน้าที่แทน ไอเอฟเอ็นเอ:
=IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")
โปรดทราบว่าอาจไม่แนะนำให้ระงับข้อผิดพลาดทั้งหมด เนื่องจากข้อผิดพลาดดังกล่าวทำหน้าที่เป็นการแจ้งเตือนปัญหาที่อาจเกิดขึ้นในสูตรของคุณ
ด้านบนคือเนื้อหาที่เกี่ยวข้องทั้งหมดที่เกี่ยวข้องกับฟังก์ชัน INDEX และ MATCH ใน Excel ฉันหวังว่าคุณจะพบว่าบทช่วยสอนนี้มีประโยชน์ หากคุณต้องการสำรวจเคล็ดลับและคำแนะนำเพิ่มเติมเกี่ยวกับ Excel โปรดคลิกที่นี่ เพื่อเข้าถึงคอลเลคชันบทช่วยสอนมากมายกว่าพันบทของเรา
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน
Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...
แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)
- หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
- ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
- เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
- นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
สารบัญ
- วิธีใช้ INDEX และ MATCH ใน Excel
- ฟังก์ชัน INDEX
- ฟังก์ชัน MATCH
- รวม INDEX และ MATCH
- ตัวอย่าง INDEX และ MATCH
- การค้นหาแบบสองทาง
- ค้นหาด้านซ้าย
- การค้นหาแบบพิจารณาตัวพิมพ์เล็กและตัวพิมพ์ใหญ่
- การแข่งขันที่ใกล้เคียงที่สุด
- ค้นหาด้วยหลายเกณฑ์
- ค้นหาข้ามหลายคอลัมน์
- ค้นหาค่าแรกที่ไม่ว่าง
- ค้นหาค่าตัวเลขแรก
- ค้นหาการเชื่อมโยง MAX หรือ MIN
- เคล็ดลับ: ปรับแต่งข้อความแสดงข้อผิดพลาด #N/A ของคุณเอง
- เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
- ความคิดเห็น