Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel สำหรับเว็บ Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel สำหรับ Windows Phone 10

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

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

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

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

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

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

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

ไวยากรณ์

หากต้องการสร้างไวยากรณ์สำหรับ 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 Tech Community หรือรับการสนับสนุนใน ชุมชน

ดูเพิ่มเติม

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

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

สํารวจสิทธิประโยชน์ของการสมัครใช้งาน เรียกดูหลักสูตรการฝึกอบรม เรียนรู้วิธีการรักษาความปลอดภัยอุปกรณ์ของคุณ และอื่นๆ

ชุมชนช่วยให้คุณถามและตอบคําถาม ให้คําติชม และรับฟังจากผู้เชี่ยวชาญที่มีความรู้มากมาย