เหตุใด VLOOKUP จึงล้มเหลวในบางครั้ง

VLOOKUP เชื่อถือได้ แต่ก็ล้มเหลวได้ในบางกรณีที่คาดเดาได้ ข้อผิดพลาดส่วนใหญ่จะไม่ใช่เรื่องลึกลับเมื่อคุณเข้าใจกฎต่าง ๆ

ในบทเรียนนี้ คุณจะได้เรียนรู้สาเหตุทั่วไปของการค้นหาที่ผิดพลาด และวิธีแก้ไขแต่ละกรณีอย่างถูกต้อง การเข้าใจเรื่องเหล่านี้จะช่วยเปลี่ยนข้อผิดพลาด #N/A และ #REF! ที่ดูสับสนให้กลายเป็นปัญหาที่แก้ได้อย่างรวดเร็วและง่ายดาย

VLOOKUP ค้นหาได้เฉพาะใน คอลัมน์ซ้ายสุด ของช่วงตาราง และส่งค่ากลับไปทาง ขวา เท่านั้น ไม่สามารถค้นหาค่าแล้วส่งค่าจากคอลัมน์ทางซ้ายกลับมาได้

หากรหัสประจำตัวอยู่ในคอลัมน์ C แต่ชื่อที่คุณต้องการอยู่ในคอลัมน์ A VLOOKUP จะย้อนกลับไปค้นหาไม่ได้ คุณมีสองทางเลือก ได้แก่ จัดเรียงคอลัมน์ใหม่ให้คอลัมน์ที่ใช้ค้นหาอยู่ก่อน หรือใช้ INDEX-MATCH หรือ XLOOKUP ซึ่งค้นหาได้ทุกทิศทาง

หมายเลขดัชนีคอลัมน์ จะนับจากด้านซ้ายของช่วงตาราง ไม่ใช่จากแผ่นงาน ข้อผิดพลาดที่พบบ่อยคือใช้ตัวอักษรคอลัมน์ของแผ่นงานเป็นตัวเลข

หากช่วงของคุณคือ C1:F10 และต้องการคอลัมน์ F คอลัมน์นั้นคือคอลัมน์ลำดับที่ 4 ของช่วง ดังนั้นดัชนีจึงเป็น 4 ไม่ใช่ 6 การนับจากขอบที่ไม่ถูกต้องจะส่งคืนช่องข้อมูลผิด หรือหากตัวเลขมากกว่าความกว้างของช่วง ก็จะเกิดข้อผิดพลาด #REF!

หากหมายเลขดัชนีคอลัมน์มากกว่าจำนวนคอลัมน์ในช่วงตาราง VLOOKUP จะส่งคืน #REF!

ตัวอย่างเช่น การขอคอลัมน์ที่ 5 จากช่วงสามคอลัมน์ A1:C10 เป็นสิ่งที่ทำไม่ได้:

แก้ไขโดยขยายช่วงตารางให้ครอบคลุมคอลัมน์ที่ต้องการ หรือแก้ดัชนีให้เป็นหมายเลขคอลัมน์ที่มีอยู่จริงภายในช่วง

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

วิธีแก้ไขนั้นง่ายและควรทำเป็นนิสัย: ใส่ FALSE เสมอสำหรับการค้นหาแบบตรงกันทุกประการ

ค่าที่ใช้ค้นหา "A100" จะไม่ตรงกับ "A100 " ที่มีช่องว่างต่อท้าย ข้อมูลที่นำเข้ามักมีความแตกต่างที่มองไม่เห็นเช่นนี้อยู่มาก

อาการคือมีค่าอยู่ชัดเจน แต่คุณกลับได้รับ #N/A ให้ทำความสะอาดข้อมูลทั้งสองฝั่งด้วย TRIM เพื่อลบช่องว่างที่เกินมา

หากค่าที่ใช้ค้นหาเป็นตัวเลข 100 แต่ตารางจัดเก็บรหัสเป็นข้อความ "100" หรือในทางกลับกัน ค่าทั้งสองจะไม่ตรงกันและคุณจะได้รับ #N/A

ให้สังเกตสามเหลี่ยมสีเขียวเล็ก ๆ หรือตัวเลขที่จัดชิดซ้าย ซึ่งเป็นสัญญาณว่าข้อมูลถูกจัดเก็บเป็นข้อความ วิธีแก้คือแปลงชนิดข้อมูล โดยครอบข้อความด้วย VALUE() เพื่อเปลี่ยนให้เป็นตัวเลข หรือเชื่อมสตริงว่างเข้ากับตัวเลขด้วย &"" เพื่อเปลี่ยนให้เป็นข้อความ เพื่อให้ข้อมูลทั้งสองฝั่งมีชนิดเดียวกัน

หากคุณลืมล็อกช่วงตาราง การคัดลอกสูตรลงด้านล่างจะลากช่วงออกจากข้อมูลของคุณ ช่วง A1:C100 ในแถวที่ 2 จะกลายเป็น A2:C101 ในแถวที่ 3 แล้วเป็น A3:C102 ทำให้พลาดข้อมูลบางแถวไปเรื่อย ๆ

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

ข้อผิดพลาดแต่ละรายการชี้ไปยังสาเหตุที่แตกต่างกัน:

• #N/A - ไม่พบค่า (ข้อมูลไม่ตรงกัน มีช่องว่าง ชนิดข้อมูลไม่ถูกต้อง หรือไม่มีค่านั้นอยู่จริง)
• #REF! - หมายเลขดัชนีคอลัมน์มากกว่าช่วง หรือเซลล์ที่อ้างอิงถูกลบ
• #VALUE! - อาร์กิวเมนต์มีชนิดข้อมูลไม่ถูกต้อง เช่น ดัชนีคอลัมน์เป็นค่าติดลบหรือศูนย์
• #NAME? - ชื่อฟังก์ชันสะกดผิด เช่น VLOOKP

เมื่อจับคู่ข้อผิดพลาดกับความหมายได้ เท่ากับคุณแก้ปัญหาไปแล้วครึ่งหนึ่ง

ระหว่างการแก้ไขข้อบกพร่อง คุณสามารถครอบการค้นหาเพื่อให้ผู้ใช้เห็นข้อความที่เข้าใจง่ายแทนข้อผิดพลาดโดยตรงได้ IFERROR จะดักจับข้อผิดพลาดใด ๆ แล้วส่งคืนข้อความของคุณแทน

วิธีนี้ไม่ได้แก้สาเหตุที่แท้จริง ดังนั้นควรใช้หลังจากเข้าใจแล้วว่าการค้นหาล้มเหลวเพราะเหตุใด การซ่อนข้อผิดพลาดเร็วเกินไปอาจทำให้ปัญหาข้อมูลจริงถูกมองข้าม

เมื่อการค้นหาทำงานผิดปกติ ให้ตรวจสอบรายการต่อไปนี้อย่างรวดเร็ว:

• ค่าที่ใช้ค้นหาอยู่ใน คอลัมน์แรก ของช่วงหรือไม่
• หมายเลขดัชนีคอลัมน์นับจากขอบซ้ายของช่วงและอยู่ภายในความกว้างของช่วงหรือไม่
• คุณใส่ FALSE สำหรับการจับคู่แบบตรงกันทุกประการแล้วหรือไม่
• ข้อมูลทั้งสองฝั่งมี ชนิดข้อมูล เดียวกันหรือไม่ (ข้อความกับตัวเลข) และไม่มี ช่องว่าง เกินมาหรือไม่
• คุณ ล็อกช่วงตาราง ด้วยเครื่องหมายดอลลาร์แล้วหรือไม่

การตรวจสอบรายการนี้จากบนลงล่างจะแก้ปัญหาการค้นหาส่วนใหญ่ได้ภายในไม่กี่วินาที

สาเหตุที่พบบ่อยและวิธีแก้ไข:

• ข้อจำกัดด้านคอลัมน์ซ้ายสุด - จัดเรียงคอลัมน์ใหม่ หรือใช้ INDEX-MATCH / XLOOKUP
• หมายเลขดัชนีคอลัมน์ไม่ถูกต้องหรือใหญ่เกินไป - นับจากขอบซ้ายของช่วง และขยายช่วงให้กว้างขึ้น
• ไม่ได้ใส่ FALSE - กำหนดการจับคู่แบบตรงกันทุกประการเสมอสำหรับรหัสประจำตัว
• ช่องว่างและข้อความกับตัวเลข - ทำความสะอาดด้วย TRIM และแปลงชนิดข้อมูลด้วย VALUE หรือ &""
• ไม่ได้ล็อกตาราง - ใช้ $ เพื่อให้ช่วงอยู่กับที่

อ่านรหัสข้อผิดพลาด จับคู่กับสาเหตุ แล้วใช้วิธีแก้ไข ตอนนี้คุณมีเครื่องมือครบชุดสำหรับการค้นหาที่แม่นยำแล้ว