VLOOKUP/XLOOKUP ra #N/A vì khoảng trắng

Vấn đề bạn đang gặp

Bạn có một bảng hóa đơn chỉ ghi Mã hàng. Tên hàng và Đơn giá nằm ở bảng danh mục, trên trang tính Bảng mã. Bạn dùng =VLOOKUP(C2,'Bảng mã'!$A$2:$C$9,3,FALSE) để lấy Đơn giá. Một số hàng ra đúng, một số hàng ra #N/A, dù khi nhìn bằng mắt, mã ở hóa đơn và mã trong Bảng mã giống nhau từng chữ.

#N/A ở đây không có nghĩa là công thức viết sai. Nó chỉ nói rằng Excel không tìm thấy giá trị khớp chính xác.

Cách sửa từng bước

Sửa công thức tra Đơn giá khi mã bị thừa dấu cách

1. 1

   Làm gì: Chọn một ô trống bên cạnh, ví dụ H2, và gõ =LEN(C2) rồi sao chép xuống. Mã đúng dài 5 ký tự (như BUT01), nên hàng nào ra 6 là mã đó thừa một dấu cách.

   Vì sao: Dấu cách ở cuối mã không nhìn thấy, nhưng hàm LEN đếm cả dấu cách. Con số lệch ký tự cho bạn thấy ngay hàng nào bị lỗi, thay vì đoán.

2. 2Chỗ hay sai

   Làm gì: Quay lại ô cần tra, ví dụ E2, và bọc mã bằng TRIM: =VLOOKUP(TRIM(C2),'Bảng mã'!$A$2:$C$9,3,FALSE).

   Vì sao: TRIM bỏ các dấu cách thừa ở đầu và cuối chữ trước khi VLOOKUP so sánh. Mã BUT01 kèm dấu cách thành BUT01 và khớp với Bảng mã.

3. 3

   Làm gì: Giữ nguyên FALSE ở cuối và dấu $ trong bảng tra, nhấn Enter rồi sao chép E2 xuống hết cột.

   Vì sao: FALSE vẫn cần để khớp chính xác, và dấu $ giữ bảng tra đứng yên khi sao chép. Sửa dấu cách không thay thế hai việc này.

4. 4

   Làm gì: Làm tương tự ở cột Tên hàng (đổi số thứ tự cột thành 2). Nếu dùng XLOOKUP, bọc đối số đầu bằng TRIM theo cách y hệt.

   Vì sao: XLOOKUP mặc định cũng tìm khớp chính xác, nên mã cần tìm cũng phải được làm sạch trước.

Tự hỏi mình

• Nếu bảng danh mục (không phải hóa đơn) mới là nơi có dấu cách thừa, bạn sẽ sửa ở đâu?
• Vì sao chỉ một số hàng ra #N/A mà không phải tất cả?

Một cách khác là làm sạch mã ở một cột phụ, ví dụ H2 với =TRIM(C2), rồi cho VLOOKUP tra theo cột đó. Cách này dễ kiểm tra hơn nhưng thêm một cột vào bảng.

Trường hợp thứ hai: mã là chữ ở bảng này, là số ở bảng kia

Dữ liệu xuất từ phần mềm kế toán hoặc kho thường lưu mã dạng chữ để giữ số 0 đứng đầu, ví dụ 007. Bảng danh mục do bạn tự gõ lại có thể lưu mã là số 7 và dùng định dạng 000 để hiện thành 007. Hai bên nhìn giống hệt nhau nhưng khác kiểu dữ liệu: chữ 007 không bao giờ bằng số 7, nên lần này mọi hàng ra #N/A, không chỉ một vài hàng như khi thừa dấu cách.

Tra Đơn giá khi mã hóa đơn là chữ, mã Bảng mã là số

1. 1

   Làm gì: Ở một ô trống gõ =ISNUMBER(C2) cho mã ở hóa đơn, và =ISNUMBER('Bảng mã'!A2) cho một mã ở Bảng mã.

   Vì sao: ISNUMBER cho TRUE khi giá trị là số. Một bên ra FALSE (chữ), một bên ra TRUE (số) cho thấy hai mã khác kiểu dù nhìn giống nhau.

2. 2Chỗ hay sai

   Làm gì: Đổi mã cần tìm sang số bằng VALUE ngay trong đối số đầu: =VLOOKUP(VALUE(C2),'Bảng mã'!$A$2:$C$9,3,FALSE).

   Vì sao: VALUE đổi chữ dạng số thành số: chữ 007 thành số 7, và số 7 khớp số 7 trong Bảng mã. TRIM không giúp ở đây vì dấu cách không phải nguyên nhân.

3. 3

   Làm gì: Nếu ngược lại (mã hóa đơn là số 7, Bảng mã lưu chữ 007), đổi số thành chữ đủ ba chữ số: =VLOOKUP(TEXT(C2,"000"),'Bảng mã'!$A$2:$C$9,3,FALSE).

   Vì sao: TEXT biến số thành chữ theo mã định dạng. Ba số 0 trong mã định dạng nghĩa là chữ có ba ký tự, nên 7 thành 007 và khớp chữ 007 trong Bảng mã.

4. 4

   Làm gì: Giữ FALSE và dấu $, nhấn Enter rồi sao chép công thức xuống hết cột. Làm tương tự cho cột Tên hàng (số thứ tự cột là 2).

   Vì sao: Đổi kiểu mã không thay thế FALSE (khớp chính xác) và $ (bảng tra đứng yên).

Tự hỏi mình

• Vì sao đổi mã cần tìm trong công thức an toàn hơn sửa kiểu dữ liệu của cả cột Bảng mã?

Vì sao lỗi này xảy ra

Dữ liệu gõ tay hoặc chép từ nơi khác có thể mang theo dấu cách thừa ở đầu hoặc cuối. Mắt không thấy dấu cách, nhưng với Excel, BUT01 và BUT01 (có dấu cách) là hai chuỗi khác nhau.

Microsoft cũng nhắc điều này: khi tra giá trị chữ ở cột đầu, dữ liệu không nên có dấu cách ở đầu hoặc cuối, nếu không VLOOKUP có thể trả về giá trị không mong đợi.

Với mã dạng số, nguyên nhân khác: Excel phân biệt chữ 007 và số 7, và hàm tra cứu so sánh cả kiểu dữ liệu. Microsoft khuyên không lưu số hoặc ngày dưới dạng chữ trong cột đầu của bảng tra. Vì vậy mã phải cùng kiểu ở hai bên, và cách nhanh nhất là đổi kiểu ngay trong công thức.

• Đã bọc TRIM mà vài hàng vẫn ra #N/A.

  Nguyên nhân

  Mã có thể chứa dấu cách không ngắt (ký tự 160), thường gặp khi chép từ web. Theo Microsoft, riêng TRIM không xóa được ký tự này.

  Cách sửa

  Đổi ký tự 160 thành dấu cách thường trước, rồi mới TRIM. Dữ liệu của bài tập bên dưới chỉ có dấu cách thường.

• Mọi hàng ra #N/A dù đã dùng TRIM và mã không có dấu cách.

  Nguyên nhân

  Một bên lưu mã dạng chữ, bên kia dạng số (ví dụ chữ 007 so với số 7). TRIM chỉ bỏ dấu cách, không đổi kiểu dữ liệu.

  Cách sửa

  Đổi kiểu mã cần tìm bằng VALUE (chữ thành số) hoặc TEXT (số thành chữ), ngay trong công thức tra.

• Mọi hàng ra #N/A sau khi sao chép công thức xuống.

  Nguyên nhân

  Bảng tra không có dấu $ nên trượt xuống từng hàng, không liên quan đến dấu cách.

  Cách sửa

  Cố định bảng tra, ví dụ 'Bảng mã'!$A$2:$C$9.

• Dùng LEN thấy mọi mã đều đúng độ dài mà vẫn #N/A.

  Nguyên nhân

  Dấu cách thừa có thể nằm ở bảng danh mục chứ không phải ở hóa đơn, hoặc mã thật sự không có trong danh mục.

  Cách sửa

  Chạy =LEN(A2) trên cột Mã hàng của Bảng mã, và kiểm tra mã có thật trong danh mục không.

Đọc xong, giờ thử

Luyện tập ngay (2 bài tập)

Làm trực tiếp trên lưới trong trình duyệt. Mỗi lần bấm chấm bài, DeskLab kiểm tra kết quả và gợi ý nếu bạn chưa làm đúng.

Bắt đầu luyện tập

Chấm tự động ngay trên máy bạn. Không cần đăng nhập. Tiến độ lưu trên trình duyệt này.

Trên Excel thật

• Lưới DeskLab: TRIM trong đối số đầu của VLOOKUP cho kết quả đúng trên lưới, và bài tập kiểm tra điều đó. Dữ liệu bài tập chỉ thừa dấu cách thường (ký tự 32).

• XLOOKUP: Hàm này cần Excel 2021/365. Nếu máy bạn dùng Excel 2016 hoặc 2019, hãy dùng VLOOKUP (hoặc INDEX kết hợp MATCH) với cách sửa như trên.

• Lưới DeskLab, trường hợp mã chữ so với mã số: VLOOKUP tra chữ 007 trong cột số cho #N/A trên lưới, và VALUE ở đối số đầu sửa được; bài tập 2 kiểm tra điều đó. Để biết một ô là chữ hay số, hãy dùng ISNUMBER, đừng đếm ký tự bằng LEN trên ô số có định dạng 000: trên lưới, LEN đọc chữ đang hiển thị (007), nên kết quả không cho biết ô là chữ hay số.
• Excel thật: Microsoft khuyên không lưu số hoặc ngày dưới dạng chữ trong cột đầu của bảng tra, và mô tả VALUE là hàm đổi chữ dạng số thành số, còn TEXT thêm lại số 0 đứng đầu theo mã định dạng.

Câu hỏi thường gặp

TRIM đã dùng mà VLOOKUP vẫn ra #N/A cho mọi hàng, mã nhìn giống hệt nhau thì sao?

Có thể một bên lưu mã dạng chữ (ví dụ 007) còn bên kia lưu dạng số (số 7, hiển thị 007 nhờ định dạng). Theo Microsoft, khi tra số hoặc ngày, dữ liệu ở cột đầu của bảng không nên lưu dạng chữ, nếu không VLOOKUP có thể trả về giá trị sai hoặc không mong đợi. TRIM chỉ bỏ dấu cách chứ không đổi kiểu. Hãy đổi kiểu mã cần tìm bằng VALUE (chữ thành số) hoặc TEXT (số thành chữ).

XLOOKUP có bị lỗi này không?

XLOOKUP mặc định cũng tìm khớp chính xác và trả về #N/A khi không thấy, nên mã còn dấu cách thừa vẫn không khớp. Cách sửa giống nhau: bọc mã cần tìm bằng TRIM, ví dụ XLOOKUP(TRIM(C2), vùng_tìm, vùng_trả_về).

TRIM không sửa được, mã vẫn ra #N/A thì sao?

Mã có thể chứa dấu cách không ngắt (ký tự 160), thường gặp khi chép từ trang web. Theo Microsoft, riêng TRIM không xóa được ký tự này. Hãy thử đổi ký tự 160 thành dấu cách thường trước, rồi mới TRIM.