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

นำไปใช้กับ

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel สำหรับ Windows Phone 10

หัวข้อจะอธิบายสาเหตุทั่วไปที่ทําให้ "ข้อผิดพลาด #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)

ฟังก์ชันจับคู่ Excel

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

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