Công thức Excel: VLOOKUP với số và văn bản
Công thức Excel: VLOOKUP với số và văn bản

Công thức chung
=VLOOKUP(val&"",table,col,0)
Tóm lược

Để sử dụng Hàm VLOOKUP để truy xuất thông tin từ một bảng trong đó các giá trị khóa là các số được lưu trữ dưới dạng văn bản, bạn có thể sử dụng công thức nối một chuỗi trống (“”) thành giá trị tra cứu dạng số, ép buộc nó thành văn bản. Trong ví dụ được hiển thị, công thức trong H3 là:

=VLOOKUP(id&"",planets,2,0) // returns "Earth"

Ở đâu Tôi (H2) và những hành tinh (B3: B11) là phạm vi được đặt tên.

Lưu ý: Ví dụ là một cách giải quyết vấn đề về số và văn bản không khớp, gây ra lỗi # N / A. Nếu không có sự phù hợp nào, giải pháp thay thế là không cần thiết và bạn có thể sử dụng công thức VLOOKUP bình thường.

Giải trình

Một lỗi VLOOKUP phổ biến là sự không khớp giữa số và văn bản. Thông thường nhất, cột tra cứu trong bảng chứa các giá trị số trông giống như những con số, nhưng trên thực tế là số được lưu trữ dưới dạng văn bản. Khi một số chính hãng được chuyển vào hàm VLOOKUP làm đối số đầu tiên, công thức sẽ trả về lỗi # N / A, mặc dù có vẻ như có sự trùng khớp. Màn hình dưới đây cho thấy một ví dụ về sự cố này:

Ví dụ về lỗi VLOOKUP với số và văn bản không khớp

Các số trong cột B thực sự là văn bản, vì vậy giá trị tra cứu số, 3, không thành công, mặc dù có vẻ như hàm VLOOKUP phải khớp với B5 và trả về “Earth”. Bạn có thể nhập một số dưới dạng giá trị văn bản bằng cách đặt trước số bằng một dấu nháy đơn (‘).

Giải pháp tốt nhất là đảm bảo các giá trị tra cứu trong bảng thực sự là số. Tuy nhiên, nếu bạn không có quyền kiểm soát bảng, bạn có thể sửa đổi công thức VLOOKUP để buộc giá trị tra cứu khớp với loại trong bảng. Trong ví dụ được hiển thị, chúng tôi ép buộc giá trị tra cứu số thành văn bản bằng cách nối một chuỗi trống:

=VLOOKUP(id,planets,2,0)    // original
=VLOOKUP(id&"",planets,2,0) // revised

Và công thức đã sửa đổi sẽ xử lý lỗi:

  Số vlookup và giải pháp lỗi văn bản

Bạn cũng có thể làm điều tương tự với một công thức dài hơn sử dụng Hàm TEXT để chuyển số thành văn bản:

=VLOOKUP(TEXT(id,"@"),planets,2,0)

Cả số và văn bản

Nếu bạn không thể chắc chắn khi nào bạn sẽ có số và khi nào bạn có văn bản, bạn có thể phục vụ cho cả hai tùy chọn bằng cách đặt hàm VLOOKUP trong Hàm IFERROR và sử dụng công thức xử lý cả hai trường hợp:

=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&"",planets,3,0))

Ở đây, trước tiên chúng ta thử một công thức VLOOKUP bình thường giả định cả giá trị tra cứu và cột đầu tiên trong bảng đều là số. Nếu điều đó xảy ra lỗi, chúng tôi thử lại với công thức đã sửa đổi. Nếu công thức đó cũng không thành công, hàm VLOOKUP sẽ trả về lỗi # N / A như mọi khi.

https://exceljet.net/formula/vlookup-with-numbers-and-text