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

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

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

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

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

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

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

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

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

พิจารณาใช้ INDEX/MATCH แทน

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

มีประโยชน์หลายประการของการใช้ดัชนี/การจับคู่แทนที่จะใช้ VLOOKUP:

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

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

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

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

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

ไวยากรณ์

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

=INDEX(อาร์เรย์หรือการอ้างอิง, MATCH(lookup_value,lookup_array,[match_type])

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

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

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

= INDEX (ส่งกลับค่าจาก C2: C10 ซึ่งจะตรงกับ (คะน้าซึ่งอยู่ในที่หนึ่งใน B2: อาร์เรย์ B10 ที่ค่าส่งกลับเป็นค่าแรกที่สอดคล้องกับคะน้า))

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

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

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

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

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

นอกจากนี้ให้พิจารณาใช้ฟังก์ชันCLEANหรือTRIMเพื่อล้างข้อมูลในเซลล์

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

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

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

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

วิธีแก้ไข:

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

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

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

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

วิธีแก้ไข:

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

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

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

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

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

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

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

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

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

ดูเพิ่มเติม

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

ก้าวล้ำหน้าไปกับ Microsoft 365

ต้องการความช่วยเหลือเพิ่มเติมหรือไม่

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

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

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

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

×