ค้นหาแบบสองทางด้วย INDEX-MATCH-MATCH

ลองนึกภาพตารางยอดขายรายเดือนที่มีภูมิภาคเรียงลงมาตามด้านซ้าย และมีเดือนเรียงไปตามด้านบน คุณต้องการตัวเลข ณ จุดที่ภูมิภาคที่เลือกตัดกับเดือนที่เลือก

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

เครื่องมือคลาสสิกสำหรับงานนี้คือ INDEX ที่ใช้ร่วมกับการเรียก MATCH สองครั้ง ซึ่งมักเขียนเป็น INDEX-MATCH-MATCH

INDEX ส่งคืนค่าจากช่วงตามตำแหน่ง รูปแบบเต็มคือ INDEX(array, row_num, column_num)

ระบุบล็อกเซลล์ หมายเลขแถว และหมายเลขคอลัมน์ แล้วฟังก์ชันจะส่งคืนค่าที่ตำแหน่งนั้น ตัวอย่างเช่น ในตารางที่เริ่มต้นที่ B2 การขอแถวที่ 3 และคอลัมน์ที่ 2 จะส่งคืนค่าที่อยู่ลงไป 3 แถวและข้ามไป 2 คอลัมน์ภายในบล็อกนั้น

แนวคิดสำคัญคือ INDEX ต้องการตำแหน่ง ไม่ใช่ป้ายกำกับ และ MATCH มีหน้าที่ให้ตำแหน่งดังกล่าว

MATCH ค้นหาตำแหน่งของค่าภายในแถวหรือคอลัมน์เดียว รูปแบบคือ MATCH(lookup_value, lookup_array, match_type)

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

หาก "East" เป็นรายการที่สองในช่วง A2:A5 MATCH จะส่งคืน 2 และเลข 2 นี้สามารถใช้เป็นหมายเลขแถวสำหรับ INDEX ได้

สำหรับการค้นหาแบบสองทิศทาง ให้เรียกใช้ MATCH สองครั้ง:

• MATCH หนึ่งครั้งเพื่อค้นหาว่าภูมิภาคอยู่ในแถวใด
• MATCH อีกครั้งเพื่อค้นหาว่าเดือนอยู่ในคอลัมน์ใด

จากนั้นส่งตัวเลขทั้งสองให้ INDEX MATCH สำหรับแถวจะค้นหาช่วงป้ายกำกับแนวตั้ง ส่วน MATCH สำหรับคอลัมน์จะค้นหาแถวหัวตารางแนวนอน

ผลลัพธ์คือเซลล์เดียวที่อยู่ตรงจุดตัดของแถวและคอลัมน์นั้น

ลองนึกภาพเค้าโครงนี้ ป้ายกำกับภูมิภาคอยู่ใน A2:A5 (East, West, North, South) หัวตารางเดือนอยู่ใน B1:D1 (Jan, Feb, Mar) และตัวเลขยอดขายจริงอยู่ใน B2:D5

เซลล์ข้อมูลเข้าสองเซลล์ใช้ควบคุมการค้นหา: G1 มีภูมิภาคที่คุณต้องการ และ G2 มีเดือนที่คุณต้องการ

เป้าหมายของเราคือสูตรเดียวที่อ่านค่า G1 และ G2 แล้วส่งคืนตัวเลขยอดขายที่ตรงกันจาก B2:D5

ขั้นแรกให้ค้นหาภูมิภาค MATCH จะค้นหาค่าที่พิมพ์ใน G1 จากรายการป้ายกำกับแนวตั้ง A2:A5

หาก G1 มีค่า "North" และ North เป็นป้ายกำกับลำดับที่สาม MATCH นี้จะส่งคืน 3

ตัวเลขนี้จะบอก INDEX ว่าต้องอ่านแถวใดของบล็อกข้อมูล โปรดสังเกตว่าเราค้นหา A2:A5 ซึ่งมีเฉพาะป้ายกำกับ ไม่ใช่ข้อมูล ดังนั้นตำแหน่งที่ 3 จึงตรงกับแถวข้อมูลที่สาม

ถัดไปให้ค้นหาเดือน MATCH นี้จะค้นหาค่าใน G2 จากแถวหัวตารางแนวนอน B1:D1

หาก G2 มีค่า "Feb" และ Feb เป็นหัวตารางลำดับที่สอง MATCH จะส่งคืน 2

ตัวเลขนี้จะกลายเป็นตำแหน่งคอลัมน์สำหรับ INDEX เช่นเดียวกับกรณีแถว เราค้นหาเฉพาะหัวตาราง B1:D1 เพื่อให้ตำแหน่งตรงกับคอลัมน์ข้อมูลใน B2:D5

ตอนนี้ให้ครอบการเรียก MATCH ทั้งสองครั้งไว้ภายใน INDEX บล็อกข้อมูล B2:D5 คืออาร์เรย์ MATCH สำหรับแถวจะระบุหมายเลขแถว และ MATCH สำหรับคอลัมน์จะระบุหมายเลขคอลัมน์

เมื่อ G1 เป็น "North" และ G2 เป็น "Feb" MATCH สำหรับแถวจะให้ค่า 3 และ MATCH สำหรับคอลัมน์จะให้ค่า 2 ดังนั้น INDEX จะส่งคืนค่าที่แถว 3 คอลัมน์ 2 ของ B2:D5

สูตรเดียวนี้คือการค้นหาแบบสองทิศทางที่สมบูรณ์

สมมติว่า B2:D5 มีข้อมูลดังนี้: แถวของ North คือ ม.ค. 50 ก.พ. 80 มี.ค. 65

• MATCH("North", A2:A5, 0) ส่งคืนค่า 3
• MATCH("Feb", B1:D1, 0) ส่งคืนค่า 2
• INDEX(B2:D5, 3, 2) อ่านแถวที่ 3 คอลัมน์ที่ 2 และส่งคืนค่า 80

เปลี่ยน G1 เป็น "East" หรือ G2 เป็น "Mar" แล้วสูตรทั้งหมดจะคำนวณใหม่ทันที นี่คือพลังของการใช้ MATCH สองครั้งเพื่อกำหนดค่าให้ INDEX

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

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

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

ในที่นี้ A2:A5 มีความสูง 4 แถว และ B2:D5 ก็มีความสูง 4 แถวเช่นกัน ดังนั้นผลลัพธ์ MATCH ที่เป็น 3 จึงหมายถึงแถวข้อมูลที่สามจริง ๆ หากเผลอค้นหาใน A1:A5 ซึ่งรวมแถวหัวตารางด้วย ตำแหน่งจะเลื่อนไปหนึ่งแถวและได้เซลล์ที่ไม่ถูกต้อง

ทดสอบความเข้าใจเกี่ยวกับรูปแบบการค้นหาแบบสองทาง

คุณได้เรียนรู้รูปแบบการค้นหาแบบสองทาง:

• INDEX ส่งคืนค่าตามตำแหน่งแถวและคอลัมน์ภายในบล็อก
• MATCH ครั้งแรกค้นหาแถวจากป้ายกำกับแนวตั้ง
• MATCH ครั้งที่สองค้นหาคอลัมน์จากหัวตารางแนวนอน

สูตรรวม =INDEX(data, MATCH(row), MATCH(col)) อ่านอินพุตสองค่าและส่งคืนค่าที่จุดตัดของทั้งสองค่า โปรดใช้ช่วง MATCH ที่มีขนาดเท่ากับบล็อกข้อมูล เพื่อหลีกเลี่ยงตำแหน่งที่ไม่ตรงกัน