คุณสามารถใช้ฟังก์ชัน Excel MATCH เพื่อค้นหาตำแหน่งสัมพัทธ์ของค่าที่ระบุในช่วงของเซลล์หรืออาร์เรย์
ฟังก์ชัน MATCH จะคล้ายกับฟังก์ชัน VLOOKUP เนื่องจากทั้งสองถูกจัดประเภทภายใต้ฟังก์ชันการค้นหา/อ้างอิงของ Excel VLOOKUP ค้นหาค่าเฉพาะในคอลัมน์และส่งกลับค่าในแถวเดียวกันในขณะที่ฟังก์ชัน MATCH จะค้นหาค่าบางค่าในช่วงและส่งกลับตำแหน่งของค่านั้น
ฟังก์ชัน Excel MATCH จะค้นหาค่าที่ระบุในช่วงของเซลล์หรืออาร์เรย์ และส่งกลับตำแหน่งสัมพัทธ์ของการปรากฏครั้งแรกของค่านั้นในช่วง ฟังก์ชัน MATCH สามารถใช้เพื่อค้นหาค่าหนึ่งๆ และคืนค่าที่ตรงกันโดยใช้ฟังก์ชัน INDEX (เช่นเดียวกับ Vlookup) มาดูวิธีการใช้ฟังก์ชัน Excel MATCH เพื่อค้นหาตำแหน่งของค่าการค้นหาในช่วงของเซลล์
ฟังก์ชัน Excel MATCH
ฟังก์ชัน MATCH เป็นฟังก์ชันในตัวใน Excel และใช้สำหรับระบุตำแหน่งสัมพัทธ์ของค่าการค้นหาในคอลัมน์หรือแถวเป็นหลัก
ไวยากรณ์ของฟังก์ชัน MATCH:
=MATCH(lookup_value,lookup_array,[match_type})ที่ไหน:
lookup_value – ค่าที่คุณต้องการค้นหาในช่วงของเซลล์ที่ระบุหรือในอาร์เรย์ อาจเป็นค่าตัวเลข ค่าข้อความ ค่าตรรกะ หรือการอ้างอิงเซลล์ที่มีค่า
lookup_array – อาร์เรย์ของเซลล์ที่คุณกำลังค้นหาค่า ต้องเป็นคอลัมน์เดียวหรือแถวเดียว
match_type – เป็นพารามิเตอร์ทางเลือกที่สามารถตั้งค่าเป็น 0,1 หรือ -1 และค่าเริ่มต้นคือ 1
- 0 ค้นหาการจับคู่แบบตรงทั้งหมด เมื่อไม่พบ จะส่งกลับข้อผิดพลาด
- -1 ค้นหาค่าที่น้อยที่สุดที่มากกว่าหรือเท่ากับ lookup_value เมื่ออาร์เรย์การค้นหาในลำดับจากน้อยไปมาก
- 1 ค้นหาค่าที่ใหญ่ที่สุดที่น้อยกว่าหรือเท่ากับค่า look_up เมื่ออาร์เรย์การค้นหาในลำดับจากมากไปน้อย
ค้นหาตำแหน่งของการแข่งขันที่แน่นอน
สมมติว่าเรามีชุดข้อมูลต่อไปนี้ซึ่งเราต้องการหาตำแหน่งของค่าหนึ่ง
ในตารางนี้ เราต้องการหาตำแหน่งชื่อเมือง (Memphis) ในคอลัมน์ (A2:A23) ดังนั้นเราจึงใช้สูตรนี้:
=MATCH("เมมฟิส",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
Wildcard Match
สามารถใช้สัญลักษณ์แทนในฟังก์ชัน MATCH ได้เฉพาะเมื่อตั้งค่า match_type เป็น '0' และค่าการค้นหาเป็นสตริงข้อความ คุณสามารถใช้อักขระตัวแทนในฟังก์ชัน MATCH ได้: เครื่องหมายดอกจัน (*) และเครื่องหมายคำถาม (?)
- เครื่องหมายคำถาม (?) ใช้เพื่อจับคู่อักขระเดี่ยวหรือตัวอักษรใดๆ กับสตริงข้อความ
- เครื่องหมายดอกจัน (*) ใช้เพื่อจับคู่อักขระจำนวนเท่าใดก็ได้กับสตริง
ตัวอย่างเช่น เราใช้อักขระตัวแทน '?' สองตัวใน lookup_value (Lo??n) ของฟังก์ชัน MATCH เพื่อค้นหาค่าที่ตรงกับสตริงข้อความที่มีอักขระสองตัวใดๆ (ในตำแหน่งสัญลักษณ์แทน) และฟังก์ชันจะส่งกลับตำแหน่งสัมพัทธ์ของค่าที่ตรงกันในเซลล์ E5
=MATCH("หล่อ??n",A2:A22,0)
คุณสามารถใช้ (*) wildcard ในลักษณะเดียวกับ (?) แต่เครื่องหมายดอกจันใช้เพื่อจับคู่อักขระจำนวนเท่าใดก็ได้ในขณะที่เครื่องหมายคำถามใช้เพื่อจับคู่อักขระตัวเดียว
ตัวอย่างเช่น หากคุณใช้ 'sp*' ฟังก์ชันอาจจับคู่กับลำโพง ความเร็ว หรือสปีลเบิร์ก เป็นต้น แต่ถ้าฟังก์ชันพบค่าหลายค่า/ซ้ำกันที่ตรงกับค่าการค้นหา ฟังก์ชันจะส่งคืนเฉพาะตำแหน่งของค่าแรกเท่านั้น
ในตัวอย่าง เราป้อน “Kil*o” ในอาร์กิวเมนต์ lookup_value ดังนั้นฟังก์ชัน MATCH() จึงค้นหาข้อความที่มีคำว่า 'Kil' อยู่ต้นทาง 'o' ต่อท้าย และจำนวนอักขระใดๆ ในระหว่างนั้น 'Kil*o' จะจับคู่ Kilimanjaro ในอาร์เรย์ ดังนั้นฟังก์ชันจะคืนค่าตำแหน่งสัมพัทธ์ของ Kilimanjaro ซึ่งเท่ากับ 16
INDEX และ MATCH
ฟังก์ชัน MATCH ไม่ค่อยได้ใช้เพียงอย่างเดียว มักจับคู่กับฟังก์ชันอื่นๆ เพื่อสร้างสูตรที่มีประสิทธิภาพ เมื่อฟังก์ชัน MATCH ถูกรวมเข้ากับฟังก์ชัน INDEX จะสามารถดำเนินการค้นหาขั้นสูงได้ หลายคนยังคงชอบใช้ VLOOKUP เพื่อค้นหาค่า เพราะมันง่ายกว่า แต่ INDEX MATCH นั้นยืดหยุ่นและเร็วกว่า VLOOKUP
VLOOKUP สามารถค้นหาค่าในแนวตั้งเท่านั้น เช่น คอลัมน์ ในขณะที่คำสั่งผสม INDEX MATCH สามารถค้นหาได้ทั้งแนวตั้งและแนวนอน
ฟังก์ชัน INDEX ใช้เพื่อดึงค่าที่ตำแหน่งเฉพาะในตารางหรือช่วง ฟังก์ชัน MATCH จะคืนค่าตำแหน่งสัมพัทธ์ของค่าในคอลัมน์หรือแถว เมื่อรวมกันแล้ว 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 และ INDEX จะดึงคะแนน
ตอนนี้ คุณรู้วิธีใช้ฟังก์ชัน Match ใน Excel แล้ว