VLOOKUP ขึ้น #N/A แก้ยังไง? รวม 8 สาเหตุพร้อมวิธีแก้ทีละข้อ

VLOOKUP เป็นสูตรที่คนทำงานใช้มากที่สุดสูตรหนึ่งใน Excel และก็เป็นสูตรที่ทำให้ปวดหัวมากที่สุดเช่นกัน ปัญหาที่เจอบ่อยที่สุดคือ VLOOKUP ขึ้น #N/A ทั้งที่มองด้วยตาแล้วข้อมูลมีอยู่ในตารางชัด ๆ

บทความนี้สรุป 8 สาเหตุที่ทำให้ VLOOKUP หาไม่เจอ พร้อมวิธีตรวจและวิธีแก้ทีละข้อ รวมถึงทบทวนการเขียนสูตร VLOOKUP ให้ถูกตั้งแต่ต้น เพื่อให้คุณแก้ปัญหาได้เองภายในไม่กี่นาที

ทบทวนสูตร VLOOKUP ก่อนแก้ปัญหา

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

อาร์กิวเมนต์	ความหมาย
lookup_value	ค่าที่ต้องการค้นหา เช่น รหัสสินค้า
table_array	ตารางข้อมูลที่ใช้ค้นหา โดยคอลัมน์แรกต้องเป็นคอลัมน์ที่มีค่าที่ค้นหา
col_index_num	ลำดับคอลัมน์ในตารางที่ต้องการดึงผลลัพธ์ (นับคอลัมน์แรกเป็น 1)
range_lookup	FALSE หรือ 0 = ค้นหาแบบตรงตัว, TRUE หรือเว้นว่าง = ค้นหาแบบประมาณ

ตัวอย่าง: มีตารางสินค้าในชีต "สินค้า" ช่วง A2:D100 (A = รหัสสินค้า, B = ชื่อสินค้า, C = ราคา, D = คงเหลือ) ต้องการดึงราคาของรหัสในเซลล์ A2 ของชีตใบสั่งซื้อ

=VLOOKUP(A2, สินค้า!$A$2:$D$100, 3, FALSE)

เลข 3 คือคอลัมน์ที่ 3 ของตาราง (ราคา) และ FALSE คือการค้นหาแบบตรงตัว ซึ่งเป็นสิ่งที่งานเกือบทั้งหมดต้องใช้

#N/A ใน VLOOKUP หมายความว่าอะไร

#N/A ย่อมาจาก "Not Available" แปลว่า Excel หาค่าที่ค้นหาไม่เจอในคอลัมน์แรกของตาราง สาเหตุมีได้ 2 แบบ คือ ข้อมูลไม่มีอยู่จริง หรือข้อมูลมีอยู่แต่ "หน้าตาไม่เหมือนกัน" ในสายตาของ Excel แบบหลังนี้คือสิ่งที่เราจะไล่แก้กันต่อไป

8 สาเหตุที่ VLOOKUP ขึ้น #N/A และวิธีแก้

1. ลืมใส่ FALSE ท้ายสูตร

ถ้าเว้นช่อง range_lookup ไว้ Excel จะค้นหาแบบประมาณ (TRUE) ซึ่งต้องเรียงข้อมูลจากน้อยไปมากก่อน ผลคือได้ #N/A หรือที่อันตรายกว่าคือ ได้ค่าผิดโดยไม่มี error เตือน

วิธีแก้: ใส่ FALSE หรือ 0 เป็นอาร์กิวเมนต์สุดท้ายทุกครั้งที่ค้นหารหัส ชื่อ หรือข้อมูลที่ต้องตรงตัว

=VLOOKUP(A2, สินค้า!$A$2:$D$100, 3, 0)

2. ค่าที่ค้นหาไม่ได้อยู่คอลัมน์แรกของตาราง

VLOOKUP ค้นหาเฉพาะคอลัมน์ซ้ายสุดของ table_array เท่านั้น ถ้าเลือกช่วง B2:D100 แต่รหัสสินค้าอยู่คอลัมน์ A สูตรจะไปค้นในชื่อสินค้าแทน และหาไม่เจอ

วิธีแก้: เลือกตารางให้เริ่มจากคอลัมน์ที่มีค่าที่ค้นหา ถ้าคอลัมน์ผลลัพธ์อยู่ทางซ้ายของคอลัมน์ค้นหา VLOOKUP ทำไม่ได้ ให้ใช้ XLOOKUP หรือ INDEX + MATCH แทน

3. มีช่องว่างเกินที่มองไม่เห็น

"P001" กับ "P001 " (มีเว้นวรรคท้าย) ดูเหมือนกันแต่ Excel ถือว่าคนละค่า ปัญหานี้พบบ่อยมากกับข้อมูลที่พิมพ์เองหรือคัดลอกจากอีเมล

วิธีตรวจ: ใช้ =LEN(A2) เทียบความยาวของทั้งสองฝั่ง ถ้าไม่เท่ากันแสดงว่ามีอักขระเกิน

วิธีแก้: ครอบค่าที่ค้นหาด้วย TRIM หรือล้างข้อมูลต้นทางด้วยคอลัมน์ช่วย =TRIM(A2) แล้ววางแบบค่า (Paste Values) ทับ

=VLOOKUP(TRIM(A2), สินค้า!$A$2:$D$100, 3, FALSE)

4. ช่องว่างพิเศษจากระบบหรือเว็บ (Non-breaking space)

ข้อมูลที่ Export จากโปรแกรมบัญชี ระบบ ERP หรือคัดลอกจากหน้าเว็บ มักมีอักขระช่องว่างชนิดพิเศษ (CHAR(160)) ที่ TRIM ลบไม่ได้

วิธีแก้: ใช้ SUBSTITUTE แทนที่ CHAR(160) ก่อน แล้วค่อย TRIM และใช้ CLEAN ลบอักขระควบคุมที่พิมพ์ไม่ออก

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

ถ้าต้องล้างข้อมูลแบบนี้ทุกเดือน การใช้ Power Query ตั้งขั้นตอนไว้ครั้งเดียวแล้วกดรีเฟรชจะประหยัดเวลากว่ามาก

5. ตัวเลขที่ถูกเก็บเป็นข้อความ (หรือกลับกัน)

สาเหตุยอดฮิตอันดับต้น ๆ รหัสอย่าง 1001 ฝั่งหนึ่งเป็นตัวเลข อีกฝั่งเป็นข้อความ สังเกตได้จากสามเหลี่ยมสีเขียวที่มุมเซลล์ หรือข้อความชิดซ้ายในขณะที่ตัวเลขชิดขวา

วิธีตรวจ: =ISNUMBER(A2) ทั้งสองฝั่ง ถ้าได้ TRUE ฝั่งหนึ่ง FALSE อีกฝั่ง คือปัญหานี้

วิธีแก้ในสูตร:

• ค่าที่ค้นหาเป็นข้อความ แต่ตารางเป็นตัวเลข: =VLOOKUP(VALUE(A2), ...) หรือ =VLOOKUP(A2*1, ...)
• ค่าที่ค้นหาเป็นตัวเลข แต่ตารางเป็นข้อความ: =VLOOKUP(A2&"", ...)

วิธีแก้ที่ต้นทาง: เลือกคอลัมน์แล้วกดไอคอนเตือน เลือก Convert to Number หรือใช้ Data > Text to Columns แล้วกด Finish จะแปลงทั้งคอลัมน์ในครั้งเดียว

6. ไม่ล็อกช่วงตาราง เมื่อลากสูตรลงมา

ถ้าเขียน A2:D100 โดยไม่มี $ พอลากสูตรลงมาแถวถัดไป ช่วงจะเลื่อนเป็น A3:D101, A4:D102 ไปเรื่อย ๆ แถวบน ๆ ของตารางหลุดออกจากการค้นหา บางแถวจึงขึ้น #N/A ทั้งที่แถวแรกถูก

วิธีแก้: คลิกที่ช่วงในสูตรแล้วกด F4 เพื่อเปลี่ยนเป็น $A$2:$D$100 หรือแปลงตารางต้นทางเป็น Table (Ctrl + T) แล้วอ้างอิงชื่อ Table แทน

7. ช่วงตารางไม่ครอบคลุมข้อมูลใหม่

สูตรเขียนไว้ที่ A2:D100 แต่เดือนนี้มีสินค้าเพิ่มเป็น 120 รายการ รหัสใหม่จึงหาไม่เจอ

วิธีแก้: ใช้ Table (Ctrl + T) ซึ่งขยายช่วงอัตโนมัติเมื่อเพิ่มข้อมูล หรืออ้างอิงทั้งคอลัมน์ เช่น สินค้า!$A:$D

8. ข้อมูลไม่มีอยู่จริง

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

วิธีแก้: ครอบด้วย IFNA (แนะนำ) หรือ IFERROR

=IFNA(VLOOKUP(A2, สินค้า!$A$2:$D$100, 3, FALSE), "ไม่พบรหัสสินค้า")

IFNA ดีกว่า IFERROR ตรงที่จะแทนเฉพาะ #N/A เท่านั้น ถ้าสูตรผิดจุดอื่น เช่น #REF! หรือ #VALUE! ก็ยังแสดงให้เห็น ไม่ถูกซ่อนไว้จนรายงานผิดโดยไม่รู้ตัว

เทคนิคตรวจหาสาเหตุ #N/A ภายใน 1 นาที

คัดลอกรหัสตัวที่หาไม่เจอจากตารางต้นทางมาไว้ข้างกัน แล้วเทียบด้วยสูตรเหล่านี้

สูตรตรวจ	ถ้าผลไม่ตรงกัน แปลว่า	ไปที่ข้อ
=A2=สินค้า!A15	FALSE = สองค่านี้ไม่เหมือนกันในสายตา Excel	ตรวจต่อด้านล่าง
=LEN(A2) เทียบ =LEN(สินค้า!A15)	มีอักขระหรือช่องว่างเกิน	ข้อ 3–4
=ISNUMBER(A2) เทียบ =ISNUMBER(สินค้า!A15)	ชนิดข้อมูลไม่ตรงกัน (ตัวเลข/ข้อความ)	ข้อ 5
=COUNTIF(สินค้า!A:A, A2)	ได้ 0 = ไม่มีค่านี้ในตารางจริง ๆ	ข้อ 8

Error อื่นที่เจอบ่อยใน VLOOKUP

#REF!

col_index_num มากกว่าจำนวนคอลัมน์ในตาราง เช่น เลือกช่วง A:C (3 คอลัมน์) แต่ใส่เลข 4 ให้ขยายช่วงตารางหรือแก้เลขคอลัมน์

#VALUE!

col_index_num น้อยกว่า 1 หรือค่าที่ค้นหายาวเกิน 255 ตัวอักษร

ได้ค่าผิดแต่ไม่ขึ้น error

มักเกิดจากลืมใส่ FALSE (ข้อ 1) หรือมีคนแทรกคอลัมน์ในตารางต้นทาง ทำให้เลขคอลัมน์ที่ใส่ไว้ชี้ไปผิดคอลัมน์ ปัญหาหลังนี้แก้ถาวรได้ด้วยการใช้ XLOOKUP ซึ่งเลือกคอลัมน์ผลลัพธ์โดยตรง ไม่ต้องใช้เลขคอลัมน์

ควรเปลี่ยนจาก VLOOKUP ไปใช้ XLOOKUP ไหม

ถ้าทุกคนที่เปิดไฟล์ใช้ Microsoft 365 หรือ Excel 2021 ขึ้นไป แนะนำให้เปลี่ยน เพราะ XLOOKUP ค้นหาแบบตรงตัวเป็นค่าเริ่มต้น (ไม่มีปัญหาลืม FALSE) ค้นหาไปทางซ้ายได้ ไม่พังเมื่อแทรกคอลัมน์ และมีช่องแสดงข้อความเมื่อหาไม่เจอในตัว อ่านวิธีใช้ทีละขั้นได้ที่ XLOOKUP คืออะไร สอนใช้แทน VLOOKUP พร้อมตัวอย่าง

แต่ถ้าต้องส่งไฟล์ให้คนที่ใช้ Excel 2019 หรือเก่ากว่า VLOOKUP ที่เขียนถูกวิธีตามบทความนี้ยังเป็นตัวเลือกที่ใช้ได้ดี

คำถามที่พบบ่อยเกี่ยวกับ VLOOKUP

VLOOKUP ข้ามชีตหรือข้ามไฟล์ได้ไหม

ได้ อ้างอิงชื่อชีตนำหน้าช่วง เช่น สินค้า!$A$2:$D$100 ถ้าข้ามไฟล์ ให้เปิดทั้งสองไฟล์แล้วคลิกเลือกช่วงจากอีกไฟล์ Excel จะใส่ชื่อไฟล์ให้เอง แต่ถ้าย้ายหรือเปลี่ยนชื่อไฟล์ต้นทาง ลิงก์จะขาด

VLOOKUP แยกตัวพิมพ์เล็ก-ใหญ่ไหม

ไม่แยก "abc" กับ "ABC" ถือเป็นค่าเดียวกัน

ถ้ามีรหัสซ้ำในตาราง VLOOKUP จะคืนค่าไหน

คืนค่าจากแถวแรกที่เจอเสมอ ถ้าต้องการรายการล่าสุดให้ใช้ XLOOKUP แบบค้นหาจากล่างขึ้นบน หรือถ้าต้องการรวมยอดของรหัสที่ซ้ำ ให้ใช้ SUMIFS แทน

ทำไมลากสูตรแล้วได้ค่าเดิมทุกแถว

อาจตั้งค่าการคำนวณเป็น Manual ไว้ ไปที่ Formulas > Calculation Options แล้วเลือก Automatic หรือกด F9 เพื่อคำนวณใหม่

สรุป

เมื่อ VLOOKUP ขึ้น #N/A ให้ตรวจตามลำดับนี้: ใส่ FALSE หรือยัง, ค่าที่ค้นหาอยู่คอลัมน์แรกไหม, มีช่องว่างเกินไหม, ชนิดข้อมูลตรงกันไหม, ล็อกช่วงด้วย $ หรือยัง และช่วงครอบคลุมข้อมูลใหม่หรือไม่ ส่วนใหญ่จะเจอสาเหตุภายใน 3 ข้อแรก

ปัญหาเหล่านี้เป็นเรื่องที่ผู้เรียนถามบ่อยที่สุดในห้องอบรม ถ้าอยากใช้ VLOOKUP, XLOOKUP, INDEX + MATCH ร่วมกับ SUMIFS และ Pivot Table ได้อย่างมั่นใจ ดูรายละเอียดที่ หลักสูตร Excel Advance ซึ่งฝึกจากข้อมูลตัวอย่างจริง 2 วัน หรือถ้าต้องการจัดอบรมให้ทั้งทีม อ่านวิธีเลือกหลักสูตรอบรม Excel สำหรับองค์กร และดูรายละเอียดอบรม In-house