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

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

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

เคล็ดลับ: อ้างอิงไปยังบัตรอ้างอิงโดยสรุป: เคล็ดลับวิธีแก้ปัญหา VLOOKUPซึ่งแสดงปัญหา #NA ในไฟล์ PDF ที่สะดวกสาเหตุทั่วไป คุณสามารถแชร์ PDF กับผู้อื่น หรือพิมพ์สำหรับการอ้างอิงของคุณเอง

ปัญหา: ค่าการค้นหาไม่ได้อยู่ในคอลัมน์แรกในอาร์กิวเมนต์ table_array

ข้อจำกัดด้านเดียวของ VLOOKUP ได้ว่า จะสามารถเฉพาะหาค่าบนคอลัมน์ด้านซ้ายสุดในอาร์เรย์ตาราง ถ้าค่าการค้นหาของคุณไม่ได้อยู่ในคอลัมน์แรกของอาร์เรย์ คุณจะเห็นข้อผิดพลาด# n/a

ในตารางต่อไปนี้ เราต้องการเรียกใช้จำนวนหน่วยที่ขายไปของ Kale

ข้อผิดพลาด #NA ใน VLOOKUP: ค่าการค้นหาไม่ได้อยู่ในคอลัมน์แรกของอาร์เรย์ตาราง

ข้อผิดพลาด# n/a ผลลัพธ์เนื่องจากค่าการค้นหา"Kale"ปรากฏในคอลัมน์สอง (พืช) ของอาร์กิวเมนต์table_array A2:C10 ในกรณีนี้ Excel จะหาในคอลัมน์ A ไม่คอลัมน์ b

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

ให้พิจารณาใช้ดัชนี/ที่ตรงกันแทน

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

มีประโยชน์หลายประการของการใช้ INDEX/MATCH แทนการใช้ VLOOKUP:

  • กับ INDEX และ MATCH ค่าส่งกลับจำเป็นไม่อยู่ในคอลัมน์เดียวกันเป็นคอลัมน์การค้นหา นี่คือแตกต่างจากฟังก์ชัน VLOOKUP ซึ่งมีค่าที่ส่งกลับจะอยู่ในช่วงระบุไว้ ฉันไม่นี้จึงสำคัญ ด้วยฟังก์ชัน VLOOKUP คุณจะต้องทราบจำนวนคอลัมน์ที่ประกอบด้วยค่าส่งกลับ ในขณะนี้อาจดูเหมือนไม่เรื่องที่ท้าทาย อาจเป็นยุ่งยากเมื่อคุณมีตารางขนาดใหญ่ และจำเป็นต้องนับจำนวนของคอลัมน์ นอกจากนี้ ถ้าคุณเพิ่ม/เอาคอลัมน์ในตารางของคุณ ได้นับใหม่ และอัปเดอาร์กิวเมนต์col_index_num ด้วย INDEX และ MATCH นับไม่จำเป็นต้องเป็นคอลัมน์การค้นหาอยู่แตกต่างจากคอลัมน์ที่มีค่าส่งกลับ

  • ด้วย INDEX และ MATCH คุณสามารถระบุแถวหรือคอลัมน์ในอาร์เรย์นั่นคือ หรือระบุทั้งสองอย่างได้ นั่นหมายความว่า คุณสามารถค้นหาค่าทั้งแนวตั้ง และแนวนอน

  • สามารถใช้ INDEX และ MATCH เพื่อค้นหาค่าในคอลัมน์ใดก็ได้ ไม่เหมือนกับ VLOOKUP — ในซึ่ง คุณสามารถเฉพาะค้นหาค่าในคอลัมน์แรกในตารางซึ่งจะใช้งานได้ถ้าค่าการค้นหาของคุณอยู่ในคอลัมน์แรก ล่าสุด หรือที่ใดก็ได้ระหว่าง INDEX และ MATCH

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

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

ไวยากรณ์

เมื่อต้องการใช้อาร์กิวเมนต์อาร์เรย์/อ้างอิงจากฟังก์ชัน INDEX และซ้อนไวยากรณ์ที่ตรงกันภายในจะสร้างไวยากรณ์สำหรับ ดัชนี/MATCH ฟอร์มการดำเนินการนี้:

= INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])

ลองใช้ ดัชนี/MATCH เพื่อแทน VLOOKUP จากตัวอย่างข้างต้น ไวยากรณ์จะมีลักษณะดังนี้:

=INDEX(C2:C10,MATCH(B13,B2:B10,0))

ในภาษาอังกฤษแบบง่ายนั่นหมายความว่า:

= ดัชนี (ส่งกลับค่าจาก C2:C10 ที่จะจับคู่ (Kale ที่อยู่ที่ใดที่หนึ่งในอาร์เรย์ b2: b10 ที่ค่าส่งกลับเป็นค่าแรกที่สอดคล้องกับ Kale))

ฟังก์ชัน INDEX และ MATCH สามารถใช้แทน VLOOKUP

สูตรจะค้นหาค่าแรกใน C2:C10 ที่สอดคล้องกับ Kale (ใน B7) และส่งกลับค่าใน C7 (100) ซึ่งเป็นค่าแรกที่ตรงกับ Kale

ปัญหา: ไม่พบค่าที่ตรงกัน

เมื่ออาร์กิวเมนต์range_lookupเป็น FALSE — และ VLOOKUP ไม่สามารถค้นหาการตรงกันในข้อมูลของคุณซึ่งจะส่งกลับข้อผิดพลาด# n/a

การแก้ไขปัญหา: ถ้าคุณไม่แน่ใจว่า ข้อมูลเกี่ยวข้องอยู่ในสเปรดชีตของคุณ และ VLOOKUP คือ catching ใช้เวลาเพื่อตรวจสอบว่า ไม่มีเซลล์ที่อ้างอิงถึงช่องว่างที่ซ่อนอยู่หรืออักขระที่ไม่พิมพ์ออกมา นอกจากนี้ ให้แน่ใจว่า เซลล์ตามชนิดข้อมูลที่ถูกต้อง ตัวอย่างเช่น เซลล์ที่ มีตัวเลขควรถูกจัดรูปแบบเป็นตัวเลขและไม่ข้อความ

นอกจากนี้ พิจารณาใช้ฟังก์ชันแท็บCLEANหรือตัดแต่งเพื่อล้างข้อมูลในเซลล์

ปัญหา: ค่าการค้นหามีค่าน้อยกว่าค่าที่น้อยที่สุดในอาร์เรย์

ถ้าอาร์กิวเมนต์range_lookupถูกตั้งค่าเป็น TRUE — และค้นหาค่ามีค่าน้อยกว่าค่าน้อยที่สุดในอาร์เรย์ซึ่งคุณจะเห็นข้อผิดพลาด# n/a ลักษณะที่เป็นจริงสำหรับตรงกันในอาร์เรย์ และส่งกลับค่าที่ใกล้โดยประมาณค่าน้อยกว่าค่าการค้นหา

ในตัวอย่างต่อไปนี้ ค่าการค้นหาคือ 100 แต่ไม่มีค่าในช่วง B2:C10 ที่น้อยกว่า 100 จึงเป็นข้อผิดพลาด

ข้อผิดพลาด N/A ใน VLOOKUP เมื่อค่าการค้นหาน้อยกว่าค่าที่น้อยที่สุดในอาร์เรย์

วิธีแก้ไข:

  • แก้ไขค่าการค้นหาตามความจำเป็น

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

ปัญหา: คอลัมน์การค้นหาไม่ได้เรียงตามลำดับจากน้อยไปหามาก

ถ้าอาร์กิวเมนต์range_lookupถูกตั้งค่าเป็น TRUE — และหนึ่งคอลัมน์การค้นหาของคุณถูกเรียงลำดับตามลำดับ (A-z) ลำดับซึ่งคุณจะเห็นข้อผิดพลาด# n/a

วิธีแก้ไข:

  • เปลี่ยนฟังก์ชัน VLOOKUP เพื่อค้นหาการตรงกันทุกประการ เมื่อต้องการดำเนินดังกล่าว ตั้งอาร์กิวเมนต์range_lookupเป็นFALSE เรียงลำดับไม่จำเป็นสำหรับ FALSE

  • ใช้ฟังก์ชัน INDEX/MATCH เพื่อค้นหาค่าในตารางที่ไม่มีการจัดเรียง

ปัญหา: ค่าเป็นเลขจุดทศนิยมลอยตัวขนาดใหญ่

ถ้าคุณมีค่าเวลาหรือตัวเลขทศนิยมขนาดใหญ่ในเซลล์ Excel ส่งกลับข้อผิดพลาด# n/a เนื่องจากจุดทศนิยมลอย เลขทศนิยมที่เป็นตัวเลขที่ตามหลังจุดทศนิยม (Excel เก็บค่าเวลาเป็นเลขทศนิยม) Excel ไม่สามารถเก็บตัวเลขที่ มีจุดลอยที่มีขนาดใหญ่ ดังนั้นสำหรับฟังก์ชันการทำงานอย่างถูกต้อง ลอยตัวชี้ ตัวเลขจะถูกปัดเศษเป็นทศนิยม 5

วิธีแก้ไข: ย่อตัวเลขให้สั้นลงโดยปัดเศษเป็นทศนิยมห้าตำแหน่งด้วยฟังก์ชัน ROUND

คุณมีคำถามที่เกี่ยวกับฟังก์ชันโดยเฉพาะหรือไม่

โพสต์คำถามในฟอรั่มชุมชน Excel

ช่วยเราปรับปรุง Excel

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

ดูเพิ่มเติม

ขยายทักษะ Office ของคุณ
สำรวจการฝึกอบรม
รับฟีเจอร์ใหม่ก่อนใคร
เข้าร่วม Office Insider

ข้อมูลนี้เป็นประโยชน์หรือไม่

ขอบคุณสำหรับคำติชมของคุณ!

ขอขอบคุณสำหรับคำติชมของคุณ! เราคิดว่าอาจเป็นประโยชน์ที่จะให้คุณได้ติดต่อกับหนึ่งในตัวแทนฝ่ายสนับสนุน Office ของเรา

×