ค้นหาตำแหน่งด้วย MATCH

หาก INDEX ดึงค่าตามตำแหน่ง MATCH จะทำหน้าที่ตรงกันข้าม โดยบอกคุณว่าค่าอยู่ที่ ตำแหน่งใด

คุณระบุค่าที่ต้องการค้นหาและช่วงที่จะค้นหาให้ MATCH จากนั้นฟังก์ชันจะส่งกลับตัวเลข ซึ่งคือตำแหน่งที่พบค่านั้น

MATCH จะไม่ส่งกลับตัวค่าเอง แต่จะส่งกลับเฉพาะตำแหน่ง ตัวเลขนี้ตรงกับสิ่งที่ INDEX ต้องใช้ในการทำงาน

MATCH รับอาร์กิวเมนต์สามรายการ:

• ค่าที่ค้นหา — ค่าที่คุณต้องการค้นหา
• อาร์เรย์สำหรับค้นหา — ช่วงที่จะค้นหา
• ประเภทการจับคู่ — วิธีจับคู่: 0, 1 หรือ -1

การเรียกใช้ทั่วไปมีลักษณะดังนี้ =MATCH("Cherry", B2:B6, 0) โดย 0 ที่อยู่ท้ายสุดหมายถึง "ค้นหาการจับคู่ที่ตรงกันทุกประการ"

สำหรับการค้นหาทั่วไป แทบทุกครั้งคุณจะใช้ 0

สมมติว่า B2:B6 มีค่าแอปเปิล กล้วย เชอร์รี อินทผลัม และมะเดื่อ เมื่อเรียกใช้ =MATCH("Cherry", B2:B6, 0) ผลลัพธ์คือ 3

เหตุใดจึงเป็น 3 เพราะเชอร์รีเป็นรายการที่ 3 ในช่วง MATCH นับจากจุดเริ่มต้นของ B2:B6 และพบเชอร์รีที่ตำแหน่ง 3

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

อาร์กิวเมนต์ที่สามจะควบคุมวิธีที่ MATCH ใช้ค้นหา:

• 0 — การจับคู่ที่ตรงกันทุกประการ ต้องพบค่าที่ตรงกันพอดี โดยข้อมูลจะเรียงลำดับอย่างไรก็ได้
• 1 — ค่าที่มากที่สุดซึ่งน้อยกว่าหรือเท่ากับค่าที่ค้นหา ข้อมูลต้องเรียงจากน้อยไปมาก
• -1 — ค่าที่น้อยที่สุดซึ่งมากกว่าหรือเท่ากับค่าที่ค้นหา ข้อมูลต้องเรียงจากมากไปน้อย

สำหรับการค้นหารายการเฉพาะ เช่น รหัสผลิตภัณฑ์หรือชื่อ ให้ใช้ 0 เสมอ โหมดอื่นเหมาะสำหรับช่วงตัวเลขที่เรียงลำดับแล้ว

MATCH ทำงานกับตัวเลขได้ดีเช่นเดียวกับข้อความ หาก C2:C8 มี ID ของคำสั่งซื้อและคุณต้องการทราบว่า ID 5042 อยู่ที่ใด ให้เขียน =MATCH(5042, C2:C8, 0)

หาก 5042 อยู่ในแถวที่ 4 ของช่วงนั้น MATCH จะส่งกลับ 4

ค่าที่ค้นหายังสามารถเป็นการอ้างอิงเซลล์ได้ด้วย =MATCH(E1, C2:C8, 0) จะค้นหา ID ใดก็ตามที่ป้อนใน E1 ดังนั้นการค้นหาจะอัปเดตเมื่อคุณเปลี่ยนค่าใน E1

MATCH ไม่สนใจว่าช่วงของคุณจะเรียงตามแนวตั้งหรือแนวนอน ฟังก์ชันเพียงนับตำแหน่งเท่านั้น

สมมติว่าแถวที่ 1 มีหัวเดือนใน B1:M1 ได้แก่ ม.ค. ก.พ. มี.ค. และอื่น ๆ หากต้องการค้นหาว่าคอลัมน์ใดมีค่า "มี.ค." ให้เขียน =MATCH("Mar", B1:M1, 0) ซึ่งจะส่งกลับ 3

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

หากค่าที่คุณค้นหาไม่มีอยู่ในช่วง MATCH จะส่งกลับข้อผิดพลาด #N/A ซึ่งหมายถึง "ไม่มีข้อมูล"

เช่น =MATCH("Mango", B2:B6, 0) เมื่อใช้กับรายการที่ไม่มีมะม่วง จะส่งกลับ #N/A

นี่เป็นการทำงานตามปกติ ไม่ใช่สูตรเสีย ภายหลังคุณจะครอบการค้นหาด้วย IFNA หรือ IFERROR เพื่อแสดงข้อความที่เข้าใจง่ายแทนข้อผิดพลาดดิบ

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

ดังนั้นไม่ว่ารายการภูมิภาคของคุณจะเรียงตามตัวอักษรหรือสลับกันอย่างไม่เป็นระเบียบ =MATCH("West", A2:A20, 0) ก็ยังค้นหาภูมิภาคตะวันตกได้อย่างถูกต้อง

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

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

หาก A2:A6 มีค่า 0, 100, 500, 1000, 5000 และคุณเรียกใช้ =MATCH(750, A2:A6, 1) ผลลัพธ์คือ 3 เพราะ 500 เป็นค่าเกณฑ์สูงสุดที่ไม่เกิน 750

นี่คือวิธีที่ตารางคะแนนและตารางราคาจัดค่าให้เข้ากับช่วงที่ถูกต้อง

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

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

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

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

เช่น ค้นหาว่าแถวใดมีค่า "ตะวันออก" ด้วย =MATCH("East", A2:A10, 0) และค้นหาว่าคอลัมน์ใดมีค่า "ไตรมาส 3" ด้วย =MATCH("Q3", B1:E1, 0)

ตัวเลขสองค่านี้ร่วมกันจะระบุเซลล์ที่แน่นอน ซึ่งพร้อมให้ INDEX ใช้งาน

ตรวจสอบให้แน่ใจว่าคุณเข้าใจว่า MATCH ส่งกลับอะไร

คุณได้เรียนรู้ว่า MATCH ส่งกลับตำแหน่งของค่าภายในช่วง:

• =MATCH(lookup_value, lookup_array, match_type)
• ใช้ 0 สำหรับการจับคู่ที่ตรงกันทุกประการ โดยข้อมูลไม่จำเป็นต้องเรียงลำดับ
• ใช้ 1 กับข้อมูลที่เรียงจากน้อยไปมากเพื่อค้นหาช่วงหรือระดับที่ถูกต้อง
• ทำงานได้ทั้งแนวตั้งและแนวนอน และส่งกลับ #N/A เมื่อไม่พบการจับคู่

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