Tuyệt chiêu sửa lỗi cho hàm Vlookup trong excel

Thứ tư - 30/10/2013 23:00
Các bạn thường hay sử dụng hàm vlookup để lấy giá trị tham chiếu từ một bảng dữ liệu khác bị lỗi do có sự khác nhau về định dạng của tham số lookup_value và lookup_array

Các bạn thường hay sử dụng  Hàm Vlookup để lấy giá trị tham chiếu từ một bảng dữ liệu khác. Tuy nhiên vì một lý do nào đó mà các giá trị trong dãy ô dùng làm tham số lookup_value bị chuyển sang dạng text value còn các giá trị cột đầu tiên của lookup_array thì được định dạng theo kiểu số (number) và ngược lại. Lúc này hàm vlookup sẽ không tìm thấy giá trị đối chiếu trùng khớp để trả về giá trị #N/A! mặc dù bạn kiểm tra giá trị của cột Lookup_Value và cột đầu của lookup_array đều chứa giá trị trùng khớp nhau nhưng hàm vẫn không trả về giá trị cần tham chiếu.

Cách giải quyết.

Ta sẽ đưa một trong 2 cột về cùng định dạng với cột kia.
 
  A B C D E
1 Lookup_Value   Lookup_Array Vlookup Result
2 9   d a j
3 12   22 d h
4 11   11 s s
5 16   12 f d
6 15   13 e g
7 14   14 r r
8 13   15 g e
9 22   16 h f
10 d   9 j a
      Match Value Value to return Returned Value
 

Trường hợp 1: E2=Vlookup($C$2:$C$10,$A$2:$D$10,4,0)

- Nếu cột chứa giá trị lookup_value (cột A) chứa giá trị lưu dạng text còn cột đầu tiên của lookup_array (Cột C) ở dạng số thì ta sẽ chuyển định dạng của các số ở dạng text sang dạng number. Cách làm như sau:
Bạn tìm đến vị trí của một ô chứa giá trị number stored as text đầu tiên trong dãy lookup_value, click chọn ô đó, sau đó rê chuột chọn chọn tất cả vùng lookup_Value. Lúc này tại ô đang hiện hành - Active Cell (là ô bạn click chuột vào ý) sẽ xuất hiện biểu tượng báo lỗi màu vàng nhạt có dấu chấm than ở giữa. bạn click chuột vào biểu tượng đó rồi chọn convert to Number như hình bên dưới.

enlightenedTips: Ô chứa giá trị số bị lưu dưới dạng chữ (Number Stored as Text) thường có một điểm nháy màu xanh ở góc trái trên cùng. và chữ số cũng nằm sát về bên trái (Chữ nằm bên trái, Số nằm lệch về bên phải theo mặc định của excel) ví dụ như ô B3 ở hình bên dưới.

Convert to Number


Cách này cũng đơn giản nhưng đôi khi nếu file có chứa số lượng lớn của các dòng thì việc chuyển từ con số dạng text sang dạng số (Number) có khả năng làm đơ máy một khoảng thời gian.

Trường hợp 2: E2=Vlookup($C$2:$C$11,$A$2:$D$10,4,0)

- Nếu cột chứa giá trị Lookup_value (Cột C) chứa giá trị lưu dạng số còn cột đầu tiên của lookup_array (Cột A) ở dạng text thì ta làm như sau:

Cách 1: Ta chèn thêm một Hàm Text để định dạng lại giá trị của cột lookup_value (Cột A) thành dạng text để chúng ăn tìm khớp với định dạng của cột A (Dạng text)

E2=Vlookup(Text(C2,0),$A$2:$D$10,4,0)
Sau đó kéo công thức ô E2 xuống đến ô E10, ta sẽ có được giá trị cần tham chiếu về.

enlightenedLưu ý, Hàm Text chỉ nhận tham số Reference là một ô (1 cell) nên ta phải chuyển tham số lookup_Value ở công thức ban đầu thành tham chiếu 1 ô (ô C2), nếu không, hàm sẽ trả về #N/A!

Cách 2: Nhân thêm 1 đơn vị và chuyển giá trị sang cột khác:

cũng với trường hợp trên, thay vì chèn thêm Hàm Text thì bạn cũng có thể chuyển cột chứa số ở dạng text bằng cách lấy giá trị của ô đó nhân cho 1.
Ví dụ ở bảng biểu bên trên thì cột A đang ở dạng text ta có thể chuyển nó thành dạng số vào cột B như sau

B2=A2*1 và kéo xuống cho đến hết ô B10. và lúc này công thức của hàm vlookup ở ô E2 như sau:

E2=Vlookup(B2:B10,$B$2:$D$10,3,0)

Tuy nhiên bạn thấy đấy ở ô A10 có chứa giá trị là ký tự (Chữ d) chứ không phải là số ở dạng chữ) do đó nếu bạn nhân cho 1 đơn vị thì kết quả cũng sẽ bị lỗi. Và dĩ nhiên Kết quả trả về ở ô E2 cũng sẽ là #N/A!

==> Đến lúc này thì bạn có thể quyết định cách giải quyết là nhân thêm 1 đơn vị để chuyển số dạng chữ sang số dạng số học hoặc chèn thêm hàm text vào lookup_value rồi nhỉ?

Chúc các bạn thành công.

 

Tác giả bài viết: Minh Phú

Chú ý: Việc đăng lại bài viết trên ở website hoặc các phương tiện truyền thông khác mà không ghi rõ nguồn http://aneedz.com là vi phạm bản quyền

Tổng số điểm của bài viết là: 16 trong 4 đánh giá

Xếp hạng: 4 - 4 phiếu bầu
Click để đánh giá bài viết

  Ý kiến bạn đọc

Bạn cần trở thành thành viên của nhóm để có thể bình luận bài viết này. Nhấn vào đây để đăng ký làm thành viên nhóm!

Những tin mới hơn

Những tin cũ hơn

Thống kê
  • Đang truy cập36
  • Máy chủ tìm kiếm3
  • Khách viếng thăm33
  • Hôm nay6,773
  • Tháng hiện tại64,539
  • Tổng lượt truy cập1,585,391
Bạn đã không sử dụng Site, Bấm vào đây để duy trì trạng thái đăng nhập. Thời gian chờ: 60 giây