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

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

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

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

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

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

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

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 จะส่งกลับค่าที่ใกล้เคียงที่สุดค่าแรกในตาราง ซึ่งอาจไม่ใช่ข้อมูลที่คุณกำลังค้นหา

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

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

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

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

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

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

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

วิธีแก้ไข:

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

• ถ้าคุณไม่สามารถเปลี่ยนค่าการค้นหา และต้องการความยืดหยุ่นมากขึ้นกับการจับคู่ค่า ให้พิจารณาใช้ 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 Tech Community หรือรับการสนับสนุนใน ชุมชน

ดูเพิ่มเติม

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

• VLOOKUP: ไม่มี #NA อีกต่อไป

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

• บัตรอ้างอิงด่วน: ฟื้นความจำเล็กๆ น้อยๆ เรื่อง VLOOKUP

• ฟังก์ชัน VLOOKUP

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

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

• ตรวจหาข้อผิดพลาดในสูตร

• ฟังก์ชันทั้งหมดของ Excel (เรียงลำดับตามตัวอักษร)

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