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

หัวข้อนี้อธิบายสาเหตุทั่วไปว่าเหตุใด VLOOKUP ของคุณอาจล้มเหลว

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

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

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

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

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

มีข้อผิดพลาดเนื่องจากค่าการค้นหา "Kale" อยู่ในคอลัมน์ที่สอง (ผลิต) ในอาร์กิวเมนต์ table_array A2:C10 ดังนั้น Excel กำลังค้นหาในคอลัมน์ A ไม่ใช่คอลัมน์ B

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

ใช้ INDEX/MATCH แทน VLOOKUP

สามารถใช้ INDEX/MATCH เมื่อ VLOOKUP ไม่ตรงตามความต้องการของคุณ ประโยชน์ที่ใหญ่ที่สุดของ INDEX/MATCH คือคุณสามารถค้นหาค่าในคอลัมน์ในตำแหน่งใดก็ได้ในตารางการค้นหา INDEX จะส่งกลับค่าจากตาราง/ช่วงที่ระบุโดยยึดตามตำแหน่ง และ MATCH จะส่งกลับตำแหน่งสัมพันธ์ของค่าในตาราง/ช่วง การใช้ 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 และซ้อนไวยากรณ์ MATCH ภายในนั้น ดังนั้น จะมีลักษณะดังนี้:

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

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

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

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

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

ฟังก์ชัน 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 และหนึ่งในคอลัมน์การค้นหาของคุณไม่ได้เรียงตามลำดับจากน้อยไปหามาก (A-Z) คุณจะเห็นข้อผิดพลาด #N/A

วิธีแก้ไข:

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

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

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

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

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

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

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

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

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

ดูเพิ่มเติม

แก้ไขข้อผิดพลาด #N/A

VLOOKUP: ไม่มี #NA เพิ่มเติม

HLOOKUP, VLOOKUP, LOOKUP ส่งกลับค่าที่ไม่ถูกต้องใน Excel

การคำนวณจุดทศนิยมอาจให้ผลลัพธ์ที่ไม่ถูกต้องใน Excel

บัตรอ้างอิงโดยสรุป: การรีเฟรช VLOOKUP

ฟังก์ชัน VLOOKUP

ภาพรวมของสูตรใน Excel

วิธีการหลีกเลี่ยงสูตรที่ใช้ไม่ได้

ใช้การตรวจสอบข้อผิดพลาดเพื่อตรวจสอบข้อผิดพลาดในสูตร

ฟังก์ชัน Excel ทั้งหมด (ตามตัวอักษร)

ฟังก์ชัน Excel ทั้งหมด (ตามประเภท)

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

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

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

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

×