วิธีการแก้ไขข้อผิดพลาด #N/A ในฟังก์ชัน INDEX/MATCH

หัวข้อนี้อธิบายสาเหตุทั่วไปที่ทําให้ "ข้อผิดพลาด #N/A" ปรากฏขึ้นเป็นผลมาจากฟังก์ชัน INDEXหรือ MATCH

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

ปัญหา: ไม่มีข้อมูลที่ตรงกัน

เมื่อฟังก์ชัน MATCH ไม่พบค่าการค้นหาในอาร์เรย์การค้นหา ฟังก์ชันจะส่งกลับข้อผิดพลาด #N/A

ถ้าคุณเชื่อว่ามีข้อมูลอยู่ในสเปรดชีต แต่ MATCH ไม่สามารถค้นหาได้ อาจเป็นเพราะ:

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

วิธีแก้ไข: เมื่อต้องการลบอักขระที่ไม่คาดคิดหรือช่องว่างที่ซ่อนอยู่ ให้ใช้ฟังก์ชัน CLEAN หรือ TRIM ตามลําดับ นอกจากนี้ ให้ตรวจสอบว่าเซลล์ถูกจัดรูปแบบเป็นชนิดข้อมูลที่ถูกต้องหรือไม่

คุณใช้สูตรอาร์เรย์โดยไม่ต้องกด Ctrl+Shift+Enter

เมื่อคุณใช้อาร์เรย์ใน INDEX, MATCH หรือการผสมระหว่างสองฟังก์ชันเหล่านั้น คุณจําเป็นต้องกด Ctrl+Shift+Enter บนคีย์บอร์ด Excel จะใส่สูตรไว้ภายในวงเล็บปีกกา {} โดยอัตโนมัติ ถ้าคุณพยายามใส่วงเล็บด้วยตัวเอง Excel จะแสดงสูตรเป็นข้อความ

หมายเหตุ: ถ้าคุณมี Microsoft 365 เวอร์ชันปัจจุบัน คุณสามารถใส่สูตรในเซลล์ผลลัพธ์ แล้วกด ENTER เพื่อยืนยันสูตรเป็นสูตรอาร์เรย์แบบไดนามิก มิฉะนั้น ต้องใส่สูตรเป็นสูตรอาร์เรย์ดั้งเดิมโดยการเลือกช่วงผลลัพธ์ ก่อนใส่สูตรในเซลล์ผลลัพธ์ แล้วกด CTRL+SHIFT+ENTER เพื่อยืนยัน Excel จะแทรกวงเล็บปีกกาที่จุดเริ่มต้นและจุดสิ้นสุดของสูตรให้คุณ สำหรับข้อมูลเพิ่มเติมเกี่ยวกับสูตรอาร์เรย์ ให้ดูที่ คำแนะนำและตัวอย่างของสูตรอาร์เรย์

ปัญหา: มีความไม่สอดคล้องกันในชนิดการจับคู่และลําดับการจัดเรียงข้อมูล

เมื่อคุณใช้ MATCH ค่าในอาร์กิวเมนต์ match_type และลําดับการจัดเรียงของค่าในอาร์เรย์การค้นหาจะมีความสอดคล้องกัน ถ้าไวยากรณ์เบี่ยงเบนจากกฎต่อไปนี้ คุณจะเห็นข้อผิดพลาด #N/A

ถ้า match_type เป็น 1 หรือไม่ระบุ ค่าใน lookup_array ควรเรียงลําดับจากน้อยไปหามาก ตัวอย่างเช่น -2, -1, 0 , 1 , 2..., A, B, C..., FALSE, TRUE เป็นต้น เป็นต้น
ถ้า match_type เป็น -1 ค่าใน lookup_array ควรเรียงลําดับจากมากไปหาน้อย

ในตัวอย่างต่อไปนี้ ฟังก์ชัน MATCH คือ

=MATCH(40,B2:B10,-1)

ฟังก์ชัน match ของ Excel

อาร์กิวเมนต์ match_type ในไวยากรณ์ถูกตั้งค่าเป็น -1 ซึ่งหมายความว่าลําดับของค่าใน B2:B10 ควรเรียงลําดับจากมากไปหาน้อยเพื่อให้สูตรทํางานได้ แต่ค่าต่างๆ เรียงลําดับจากน้อยไปหามาก และทําให้เกิดข้อผิดพลาด #N/A

โซลูชัน: เปลี่ยนอาร์กิวเมนต์ match_type เป็น 1 หรือเรียงลําดับตารางในรูปแบบจากมากไปหาน้อย จากนั้นลองอีกครั้ง