ข้ามไปที่เนื้อหาหลัก
การสนับสนุน
ลงชื่อเข้าใช้

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

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

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

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

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

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

ผลลัพธ์#N/A เนื่องจากค่าการค้นหา "Kaly" ปรากฏในคอลัมน์ที่สอง (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(Kal1 ซึ่งอยู่ที่ใดที่หนึ่งในอาร์เรย์ B2:B10 ซึ่งค่าส่งกลับเป็นค่าแรกที่สอดคล้องกับ Kaly))

ฟังก์ชัน 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 คุณสามารถค้นหาค่ามากกว่า น้อยกว่า หรือเท่ากับค่าการค้นหา For more information on using INDEX/MATCH instead of VLOOKUP, refer to the previous section in this topic.

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

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

วิธีแก้ไข:

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

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

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

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

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

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

คุณสามารถสอบถามผู้เชี่ยวชาญใน ชุมชนด้านเทคนิคของ Excel หรือ ขอความช่วยเหลือใน Answers Community

ดูเพิ่มเติม

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

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

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

คุณพึงพอใจกับคุณภาพการแปลแค่ไหน
สิ่งที่ส่งผลกระทบต่อประสบการณ์ใช้งานของคุณ

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

×