ค้นหาหลายเงื่อนไขด้วย INDEX-MATCH

บางครั้งคอลัมน์เดียวไม่สามารถระบุแถวได้อย่างไม่ซ้ำกัน คุณอาจต้องการราคาของสินค้า ในขนาดที่ระบุ หรือเงินเดือนของพนักงาน ในแผนกที่ระบุ

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

INDEX-MATCH จัดการเรื่องนี้ได้อย่างยืดหยุ่น โดยรวมเงื่อนไขต่าง ๆ เป็นการทดสอบการจับคู่เพียงครั้งเดียว โดยไม่ต้องเพิ่มคอลัมน์ช่วย

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

ตัวอย่างเช่น เซลล์ช่วยอาจมีค่า =A2&"|"&B2 ซึ่งให้ผลเป็น "Shirt|Large" จากนั้นคุณจึงใช้ MATCH ค้นหา "Shirt|Large" ในคอลัมน์ที่รวมกันนั้น

วิธีนี้ใช้ได้จริง แต่ทำให้แผ่นงานดูรก ฉากถัดไปจะแสดงวิธีข้ามการใช้คอลัมน์ช่วยไปโดยสิ้นเชิง

เคล็ดลับสำคัญคือคูณการทดสอบเงื่อนไขทั้งสองภายใน MATCH

(A2:A10=G1) ให้เป็นอาร์เรย์ TRUE/FALSE สำหรับเงื่อนไขแรก ส่วน (B2:B10=G2) ทำเช่นเดียวกันสำหรับเงื่อนไขที่สอง เมื่อนำมาคูณกันเป็น (A2:A10=G1)*(B2:B10=G2) จะได้ค่า 1 เฉพาะตำแหน่งที่ทั้งสองเงื่อนไขเป็น TRUE และได้ค่า 0 ในตำแหน่งอื่น

จากนั้น MATCH จะค้นหาค่า 1 เพื่อหาแถวที่ตรงตามทั้งสองเงื่อนไข

ในตารางคำนวณ TRUE จะทำหน้าที่เหมือน 1 และ FALSE จะทำหน้าที่เหมือน 0 การคูณค่าทั้งสองจึงเลียนแบบตรรกะ AND:

• 1 คูณ 1 = 1 (ตรงตามทั้งสองเงื่อนไข)
• 1 คูณ 0 = 0
• 0 คูณ 1 = 0
• 0 คูณ 0 = 0

ดังนั้นเฉพาะแถวที่ตรงตามทั้งสองเกณฑ์เท่านั้นที่จะให้ค่าเป็น 1 แถวอื่นทั้งหมดจะกลายเป็น 0 ค่า 1 เพียงค่าเดียวนั้นจะระบุแถวที่เราต้องการ

ตอนนี้ให้นำอาร์เรย์ที่คูณกันไปครอบด้วย MATCH โดยค้นหาค่าที่ตรงกันทุกประการคือ 1

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) จะส่งคืนตำแหน่งของแถวแรกที่ทั้งสองเงื่อนไขเป็น TRUE

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

ส่งผลลัพธ์จาก MATCH ให้ INDEX ซึ่งทำงานกับคอลัมน์ที่คุณต้องการจริง ๆ เช่น ราคาใน C2:C10

สูตรสมบูรณ์มีความหมายว่า จาก C2:C10 ให้ส่งคืนค่าที่อยู่ในแถวซึ่งสินค้าตรงกับ G1 และขนาดตรงกับ G2

นี่คือการค้นหาหลายเงื่อนไขอย่างแท้จริง โดยไม่ต้องมีคอลัมน์ช่วยและไม่ต้องจัดเรียงข้อมูลใหม่

สูตรนี้ประมวลผลอาร์เรย์ของเงื่อนไข ใน เอ็กเซลรุ่นใหม่ และ Google ชีต เพียงกด Enter ก็ใช้งานได้

ใน เอ็กเซลรุ่นเก่า (ก่อนมีอาร์เรย์ไดนามิก) คุณต้องยืนยันสูตรนี้เป็นสูตรอาร์เรย์ด้วย Ctrl+Shift+Enter ซึ่งจะเพิ่มวงเล็บปีกกา หากผลลัพธ์ไม่ถูกต้องหรือแสดงข้อผิดพลาดในเอ็กเซลรุ่นเก่า ขั้นตอนการยืนยันนี้มักเป็นสิ่งที่ขาดไป

ต้องการใช้สามเกณฑ์หรือไม่ เพียงคูณการทดสอบเพิ่มอีกหนึ่งรายการ สมมติว่าคุณต้องการจับคู่สีในคอลัมน์ D กับอินพุต G3 ด้วย

ตัวประกอบ (range=criterion) ที่เพิ่มเข้ามาแต่ละรายการจะจำกัดผลลัพธ์ให้แคบลง เฉพาะแถวที่ทุกเงื่อนไขเป็น TRUE เท่านั้นจึงจะยังมีผลคูณเป็น 1 หากมีค่าใดเป็น FALSE ผลคูณทั้งหมดจะกลายเป็น 0

รูปแบบนี้ขยายใช้ได้กับคอลัมน์จำนวนเท่าใดก็ได้ตามต้องการ

ข้อมูล: A = สินค้า, B = ขนาด, C = ราคา คุณต้องการราคาของ "Shirt" ขนาด "Large"

• G1 = "Shirt", G2 = "Large"
• อาร์เรย์เงื่อนไขจะให้ค่า 1 เฉพาะแถว Shirt+Large เช่น แถวที่ 4
• MATCH(1, ..., 0) ส่งคืนค่า 4
• INDEX(C2:C10, 4) ส่งคืนราคาของแถวนั้น

เมื่อเปลี่ยนอินพุตค่าใดค่าหนึ่ง สูตรจะค้นหาแถวที่ถูกต้องใหม่ทันที

โปรดคำนึงถึงเรื่องเหล่านี้:

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

หากมีหลายแถวที่ตรงกัน และคุณต้องการ รวมยอด ค่าเหล่านั้นแทนการดึงมาเพียงค่าเดียว SUMPRODUCT ก็เป็นทางเลือกที่เหมาะสมแทน INDEX-MATCH ที่ต้องป้อนเป็นสูตรอาร์เรย์

ฟังก์ชันนี้จะคูณอาร์เรย์เงื่อนไขเข้ากับคอลัมน์ค่า แล้วนำผลลัพธ์มาบวกกัน ดังนั้นจะมีเพียงแถวที่ตรงตามเกณฑ์ทั้งสองข้อเท่านั้นที่มีส่วนในผลรวม ไม่จำเป็นต้องกด Ctrl+Shift+Enter เพราะ SUMPRODUCT รองรับอาร์เรย์โดยตรง

ใช้ INDEX-MATCH เพื่อดึงค่าที่ตรงกันเพียงค่าเดียว และใช้ SUMPRODUCT เพื่อรวมค่าจากรายการที่ตรงกันทั้งหมด

ทดสอบความรู้เกี่ยวกับการค้นหาด้วยหลายเกณฑ์

สำหรับการค้นหาด้วยหลายเกณฑ์โดยใช้ INDEX-MATCH:

• คูณอาร์เรย์เงื่อนไขเข้าด้วยกัน: (A=G1)*(B=G2) จะให้ค่า 1 เฉพาะตำแหน่งที่ตรงตามเงื่อนไขทั้งหมด (ตรรกะ AND)
• MATCH(1, ..., 0) จะค้นหาตำแหน่งของแถวนั้น
• INDEX(returnCol, position) จะส่งกลับค่า

เพิ่มตัวประกอบ *(range=criterion) สำหรับเงื่อนไขเพิ่มเติม ตรวจสอบให้ช่วงมีความสูงเท่ากัน ยืนยันสูตรด้วย Ctrl+Shift+Enter ใน Excel รุ่นเก่า และป้องกันข้อผิดพลาดด้วย IFERROR