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