วิธีใช้ Excel Match Function

ค้นหาตำแหน่งของการจับคู่ที่แน่นอน

สมมติว่า เรามีชุดข้อมูลต่อไปนี้ซึ่งเราต้องการหาตำแหน่งของค่าที่แน่นอน

ในตารางนี้เราต้องการหาตำแหน่งชื่อเมือง (เมมฟิส) ในคอลัมน์ (A2: A23) ดังนั้นเราจึงใช้สูตรนี้:

=MATCH("memphis",A2:A23,0)

อาร์กิวเมนต์ที่สามตั้งค่าเป็น”0″เนื่องจากเราต้องการค้นหาชื่อเมืองที่ตรงกันทุกประการ ดังที่คุณเห็นว่าชื่อเมือง”เมมฟิส”ในสูตรเป็นตัวพิมพ์เล็กในขณะที่ตัวอักษรตัวแรกของชื่อเมืองอยู่ในตารางเป็นตัวพิมพ์ใหญ่ (เมมฟิส) อย่างไรก็ตามสูตรยังสามารถค้นหาตำแหน่งของค่าที่ระบุในช่วงที่กำหนดได้ เป็นเพราะฟังก์ชัน MATCH ไม่คำนึงถึงตัวพิมพ์เล็กและใหญ่

หมายเหตุ: หากไม่พบ lookup_value ในช่วงการค้นหาหรือหากคุณระบุช่วงการค้นหาที่ไม่ถูกต้อง ฟังก์ชันจะคืนค่า # N/A ข้อผิดพลาด

คุณสามารถใช้การอ้างอิงเซลล์ในอาร์กิวเมนต์แรกของฟังก์ชันแทนค่าโดยตรง สูตรด้านล่างจะค้นหาตำแหน่งของค่าในเซลล์ F2 และส่งกลับผลลัพธ์ในเซลล์ F3

ค้นหาตำแหน่งของการจับคู่โดยประมาณ

มีสองวิธีที่คุณสามารถค้นหาค่าที่ตรงกันโดยประมาณหรือแบบตรงทั้งหมดของค่าการค้นหาและส่งคืนตำแหน่ง

• วิธีหนึ่งคือการหาค่าที่น้อยที่สุดที่มากกว่าหรือเท่ากับ (การจับคู่ที่ใหญ่ที่สุดถัดไป) กับค่าที่ระบุ สามารถทำได้โดยการตั้งค่าอาร์กิวเมนต์สุดท้าย (match_type) ของฟังก์ชันเป็น”-1″

• อีกวิธีหนึ่งคือค่าที่ใหญ่ที่สุดที่น้อยกว่าหรือเท่ากับ (การจับคู่ที่เล็กที่สุดถัดไป) กับค่าที่กำหนด สามารถทำได้โดยตั้งค่า match_type ของฟังก์ชันเป็น”1″

การแข่งขันที่เล็กที่สุดถัดไป

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

ในตัวอย่างเราใช้สูตรด้านล่างเพื่อค้นหารายการที่ตรงกันที่เล็กที่สุดถัดไป:

 =MATCH (F2, D2: D23,1)  

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

นัดถัดไปที่ใหญ่ที่สุด

เมื่อตั้งค่าประเภทการจับคู่เป็น’-1’และฟังก์ชัน MATCH ไม่พบการจับคู่แบบตรงทั้งหมด ระบบจะค้นหาค่าที่น้อยที่สุดที่มากกว่าค่าที่ระบุ (ซึ่งหมายถึงค่าที่มากที่สุดรองลงมา) และส่งกลับตำแหน่ง. อาร์เรย์การค้นหาต้องเรียงลำดับจากมากไปหาน้อยสำหรับวิธีนี้ไม่เช่นนั้นจะส่งคืนข้อผิดพลาด

ตัวอย่างเช่น ป้อนสูตรต่อไปนี้เพื่อค้นหาค่าที่ตรงกันที่ใหญ่ที่สุดถัดไป:

 =MATCH (F2, D2: D23,-1)  

ฟังก์ชัน MATCH นี้ค้นหาค่าใน F2 (55) ในช่วงการค้นหา D2: D23 และเมื่อไม่พบค่าที่ตรงกันทั้งหมดจะส่งกลับตำแหน่ง (16) ของค่าที่ใหญ่ที่สุดถัดไปคือ 58/p>

การจับคู่สัญลักษณ์แทน

สัญลักษณ์แทนสามารถใช้ได้ในฟังก์ชัน MATCH ก็ต่อเมื่อ match_type ถูกตั้งค่าเป็น”0″และค่าการค้นหาเป็นสตริงข้อความ คุณสามารถใช้อักขระตัวแทนในฟังก์ชัน MATCH ได้: เครื่องหมายดอกจัน (*) และเครื่องหมายคำถาม (?)

• เครื่องหมายคำถาม (?) ใช้เพื่อจับคู่อักขระตัวเดียวหรือตัวอักษรกับสตริงข้อความ
• ดอกจัน (*) ใช้เพื่อจับคู่อักขระจำนวนเท่าใดก็ได้กับสตริง

ตัวอย่างเช่นเราใช้สัญลักษณ์แทน”?”สองตัวใน lookup_value (Lo ?? n) ของฟังก์ชัน MATCH เพื่อค้นหาค่าที่ตรงกับสตริงข้อความที่มีอักขระสองตัว (ในตำแหน่งสัญลักษณ์แทน) และฟังก์ชันจะส่งกลับตำแหน่งสัมพัทธ์ของค่าที่ตรงกันในเซลล์ E5

 =MATCH ("Lo ?? n", A2: A22,0)  

คุณสามารถใช้สัญลักษณ์แทน (*) ได้เช่นเดียวกับ (?) แต่ใช้เครื่องหมายดอกจันเพื่อจับคู่อักขระจำนวนเท่าใดก็ได้ในขณะที่ใช้เครื่องหมายคำถามเพื่อจับคู่อักขระเดี่ยวใด ๆ

ตัวอย่างเช่น หากคุณใช้’sp*’ฟังก์ชันอาจจับคู่กับลำโพง ความเร็ว หรือสปีลเบิร์ก เป็นต้น แต่ถ้าฟังก์ชันพบค่าหลายค่า/ซ้ำกันที่ตรงกับค่าการค้นหา ฟังก์ชันจะส่งคืนเฉพาะตำแหน่งของ ค่าแรก

ในตัวอย่างเราป้อน“ Kil * o” ในอาร์กิวเมนต์ lookup_value ดังนั้นฟังก์ชัน MATCH () จะค้นหาข้อความที่มี”Kil”ขึ้นต้น”o”ต่อท้ายและอักขระจำนวนเท่าใดก็ได้ที่อยู่ระหว่างนั้น”Kil * o”ตรงกับ Kilimanjaro ในอาร์เรย์ดังนั้นฟังก์ชันจึงส่งคืนตำแหน่งสัมพัทธ์ของ Kilimanjaro ซึ่งเป็น 16

ดัชนีและการจับคู่

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

VLOOKUP สามารถค้นหาค่าในแนวตั้งเท่านั้นเช่นคอลัมน์ในขณะที่คำสั่งผสม INDEX MATCH สามารถค้นหาได้ทั้งแนวตั้งและแนวนอน

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

ไวยากรณ์ของฟังก์ชัน INDEX:

 =INDEX (อาร์เรย์, row_num, [col_num],)  

อย่างไรก็ตามเรามาดูกันว่า INDEX MATCH ทำงานอย่างไรกับตัวอย่าง

ในตัวอย่างด้านล่างเราต้องการดึงคะแนน”Quiz2″สำหรับนักเรียน”Anne”ในการทำเช่นนั้นเราจะใช้สูตรด้านล่าง:

 =INDEX (B2: F20, MATCH (H2, A2: A20,0), 3)  

INDEX ต้องการหมายเลขแถวและคอลัมน์เพื่อดึงค่า ในสูตรข้างต้น ฟังก์ชัน MATCH ที่ซ้อนกันจะค้นหาหมายเลขแถว (ตำแหน่ง) ของค่า’Anne'(H2) จากนั้นเราใส่หมายเลขแถวนั้นให้กับฟังก์ชัน INDEX ด้วยช่วง B2: F20 และหมายเลขคอลัมน์ (3) ซึ่งเราระบุ และฟังก์ชัน INDEX จะคืนค่าคะแนน’91’

การค้นหาแบบสองทางด้วย INDEX และ MATCH

คุณยังสามารถใช้ฟังก์ชัน INDEX และ MATCH เพื่อค้นหาค่าในช่วงสองมิติ (การค้นหาแบบสองทาง) ในตัวอย่างข้างต้น เราใช้ฟังก์ชัน MATCH เพื่อค้นหาหมายเลขแถวของค่า แต่เราป้อนหมายเลขคอลัมน์ด้วยตนเอง แต่เราสามารถค้นหาทั้งแถวและคอลัมน์ได้โดยการซ้อนฟังก์ชัน MATCH สองฟังก์ชันหนึ่งในอาร์กิวเมนต์ row_num และอีกอันในอาร์กิวเมนต์ column_num ของฟังก์ชัน INDEX

ใช้สูตรนี้สำหรับการค้นหาแบบสองทางด้วย INDEX และ MATCH:

 =INDEX (A1: F20, MATCH (H2, A2: A20,0), MATCH (H3, A1: F1,0))   

 อย่างที่เราทราบกันดีว่าฟังก์ชัน MATCH สามารถค้นหาค่าได้ทั้งในแนวนอนและแนวตั้ง ในสูตรนี้ฟังก์ชัน MATCH ที่สองในอาร์กิวเมนต์ colum_num จะค้นหาตำแหน่งของ Quiz2 (4) และส่งไปยังฟังก์ชัน INDEX และดัชนีจะดึงคะแนน
 
 ตอนนี้คุณรู้วิธีใช้ฟังก์ชัน Match ใน Excel แล้ว