Mô hình Excel Hàm VLOOKUP là một công cụ mạnh mẽ giúp bạn tìm kiếm một giá trị được chỉ định bằng cách so khớp trên cột đầu tiên của bảng hoặc một phạm vi theo chiều dọc, sau đó trả về một giá trị tương ứng từ một cột khác trong cùng một hàng. Mặc dù VLOOKUP vô cùng hữu ích nhưng đôi khi có thể khó nắm bắt đối với người mới bắt đầu. Hướng dẫn này nhằm mục đích giúp bạn thành thạo VLOOKUP bằng cách cung cấp giải thích từng bước của các đối số, ví dụ hữu ích và giải pháp cho các lỗi phổ biến bạn có thể gặp phải khi sử dụng hàm VLOOKUP.

• Từng bước giải thích các lập luận
• Cú pháp và đối số
• Ví dụ về VLOOKUP
  Đối sánh chính xác so với đối sánh gần đúng   |   Vlookup đa điều kiện
• VLOOKUP Các lỗi thường gặp & cách khắc phục
  # N / A lỗi   |   #VALUE lỗi   |   lỗi #REF   |   giá trị không chính xác

Ví dụ VLOOKUP .xlsx Lỗi VLOOKUP .xlsx

Video liên quan

Từng bước giải thích các lập luận

Như trong ảnh chụp màn hình ở trên, hàm VLOOKUP được sử dụng để tìm email dựa trên số ID đã cho. Bây giờ tôi sẽ cung cấp giải thích chi tiết về cách sử dụng VLOOKUP trong ví dụ này bằng cách chia nhỏ từng đối số theo từng bước.

Bước 1: Khởi động hàm VLOOKUP

Chọn một ô (trong trường hợp này là ô H6) để xuất kết quả, sau đó khởi động hàm VLOOKUP bằng cách nhập nội dung sau vào ô Thanh công thức.

=VLOOKUP(

Bước 2: Chỉ định giá trị tra cứu

Trước tiên, hãy chỉ định giá trị tra cứu (là giá trị bạn đang tìm kiếm) trong hàm VLOOKUP. Ở đây, tôi tham chiếu ô G6 chứa số ID nhất định 1005.

=VLOOKUP(G6

Bước 3: Chỉ định mảng bảng

Tiếp theo, chỉ định một phạm vi ô chứa cả giá trị bạn đang tìm kiếm và giá trị bạn muốn trả về. Trong trường hợp này, tôi chọn phạm vi B6:E12. Công thức bây giờ xuất hiện như sau:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Bước 4: Chỉ định cột mà bạn muốn trả về giá trị

Sau đó, chỉ định cột mà bạn muốn trả lại giá trị.

Trong ví dụ này, vì tôi cần trả lại email dựa trên số ID, ở đây tôi nhập số 4 để báo cho VLOOKUP trả về giá trị từ cột thứ tư của dải dữ liệu.

=VLOOKUP(G6,B6:E12,4

Bước 5: Tìm một đối sánh gần đúng hoặc chính xác

Cuối cùng, xác định xem bạn đang tìm kiếm đối sánh gần đúng hay đối sánh chính xác.

• Để tìm một kết hợp chuẩn xác, bạn cần sử dụng KHÔNG ĐÚNG như đối số cuối cùng.
• Để tìm một trận đấu gần đúng, Sử dụng TRUE làm đối số cuối cùng hoặc chỉ để trống.

Trong ví dụ này, tôi sử dụng FALSE để đối sánh chính xác. Công thức bây giờ trông như thế này:

=VLOOKUP(G6,B6:E12,4,FALSE

Nhấn phím Enter để có kết quả

Bằng cách giải thích từng đối số một trong ví dụ trên, cú pháp và đối số của hàm VLOOKUP giờ đây dễ hiểu hơn nhiều.

Cú pháp và đối số

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

• Tra cứu_value (bắt buộc): Giá trị (giá trị thực hoặc tham chiếu ô) mà bạn đang tìm kiếm. Hãy nhớ rằng giá trị này phải nằm trong cột đầu tiên của table_array.
• Bảng_mảng (bắt buộc): Một dải ô chứa cả cột giá trị tra cứu và cột giá trị trả về.
• chỉ mục Col_ (bắt buộc): Một số nguyên đại diện cho số cột chứa giá trị trả về. Nó bắt đầu với số 1 cho cột ngoài cùng bên trái của table_array.
• Phạm vi_lookup (tùy chọn): Giá trị logic xác định xem bạn muốn VLOOKUP tìm kết quả khớp gần đúng hay kết quả khớp chính xác.
  • Đối sánh gần đúng - Đặt đối số này thành TRUE, 1 hoặc để nó trống.
    Quan trọng: Để tìm một kết quả gần đúng, các giá trị trong cột đầu tiên của table_array phải được sắp xếp theo thứ tự tăng dần trong trường hợp hàm VLOOKUP trả về kết quả sai.
  • Kết hợp chuẩn xác - Đặt đối số này thành KHÔNG ĐÚNG or 0.

Các ví dụ

Phần này trình bày một số ví dụ để giúp bạn hiểu toàn diện hơn về hàm VLOOKUP.

Ví dụ 1: Đối sánh chính xác với đối sánh gần đúng trong VLOOKUP

Nếu bạn nhầm lẫn về đối sánh chính xác và đối sánh gần đúng khi sử dụng VLOOKUP, phần này có thể giúp bạn giải tỏa sự nhầm lẫn đó.

Đối sánh chính xác trong VLOOKUP

Trong ví dụ này, tôi sẽ tìm các tên tương ứng dựa trên điểm số được liệt kê trong phạm vi E6:E8, vì vậy tôi nhập công thức sau vào ô F6 và kéo núm điều khiển Tự động điền xuống F8. Trong công thức này, đối số cuối cùng được chỉ định là KHÔNG ĐÚNG để thực hiện tra cứu đối sánh chính xác.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Tuy nhiên, vì điểm 98 không tồn tại trong cột đầu tiên của phạm vi dữ liệu, hàm VLOOKUP trả về kết quả lỗi #N/A.

Đối sánh gần đúng trong VLOOKUP

Vẫn sử dụng ví dụ trên, nếu bạn thay đổi đối số cuối cùng thành TRUE, VLOOKUP sẽ thực hiện tra cứu đối sánh gần đúng. Nếu không tìm thấy kết quả khớp, nó sẽ tìm giá trị lớn nhất tiếp theo nhỏ hơn giá trị tra cứu và trả về kết quả tương ứng.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Vì điểm 98 không tồn tại, VLOOKUP tìm giá trị lớn nhất tiếp theo nhỏ hơn 98, là 95 và trả về tên của điểm 95 là kết quả gần nhất.

Ví dụ 2: Sử dụng VLOOKUP với nhiều tiêu chí

Phần này trình bày cách sử dụng VLOOKUP với nhiều điều kiện trong Excel. Như được hiển thị trong ảnh chụp màn hình bên dưới, nếu bạn đang cố gắng xác định mức lương dựa trên tên được cung cấp (trong ô H5) và bộ phận (trong ô H6), hãy làm theo các bước bên dưới để hoàn thành.

Bước 1: Thêm một cột trợ giúp để nối các giá trị từ các cột tra cứu

Trong trường hợp này, chúng ta cần tạo một cột trợ giúp để nối các giá trị từ Họ tên cột và bộ cột.

1. Thêm cột trợ giúp vào bên trái phạm vi dữ liệu của bạn và đặt tiêu đề cho cột này. Xem ảnh chụp màn hình:
   
   
2. Trong cột trợ giúp này, chọn ô đầu tiên bên dưới tiêu đề, nhập công thức sau vào ô Thanh công thức, và hãy nhấn đăng ký hạng mục thi.
   

   =C6&" "&D6

   
   Chú ý: Trong công thức này, chúng tôi sử dụng dấu và (&) để nối văn bản thành hai cột để tạo ra một đoạn văn bản.

   • C6 là tên đầu tiên của Họ tên cột để tham gia, D6 là khoa đầu tiên của bộ cột để tham gia.
   • Các giá trị của hai ô này được nối với nhau bằng một khoảng trắng ở giữa.
3. Chọn ô kết quả này, sau đó kéo Xử lý tự động điền xuống để áp dụng công thức này cho các ô khác trong cùng một cột.
   
   

Bước 2: Áp dụng hàm VLOOKUP với tiêu chí cho trước

Chọn một ô mà bạn muốn xuất kết quả (ở đây tôi chọn I7), nhập công thức sau vào ô Thanh công thức, và sau đó nhấn đăng ký hạng mục thi.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Kết quả

Các lỗi VLOOKUP thường gặp và cách khắc phục

Phần này liệt kê các lỗi phổ biến mà bạn có thể gặp phải khi sử dụng VLOOKUP và cung cấp các giải pháp để khắc phục chúng.

#N/A lỗi được trả về

Lỗi phổ biến nhất với VLOOKUP là lỗi #N/A, có nghĩa là Excel không thể tìm thấy giá trị bạn đang tìm kiếm. Dưới đây là một số lý do khiến hàm VLOOKUP có thể trả về lỗi #N/A.

Lý do 1: Giá trị tra cứu không có trong cột đầu tiên của table_array

Một trong những hạn chế của Excel VLOOKUP là nó chỉ cho phép bạn nhìn từ trái sang phải. Vì vậy, các giá trị tra cứu phải nằm ở cột đầu tiên của table_array.

Như được hiển thị trong ảnh chụp màn hình bên dưới, tôi muốn trả lại tên dựa trên chức danh công việc đã cho. Ở đây giá trị tra cứu (quản lý bán hàng) nằm trong cột thứ hai của table_array và giá trị trả về nằm ở bên trái của cột tra cứu, vì vậy hàm VLOOKUP trả về lỗi #N/A.

Giải pháp

Bạn có thể áp dụng bất kỳ giải pháp nào sau đây để khắc phục lỗi này.

• Sắp xếp lại các cột
  
  Bạn có thể sắp xếp lại các cột để đặt cột tra cứu vào cột đầu tiên của table_array.
• Sử dụng đồng thời các hàm INDEX và MATCH
  
  Ở đây, chúng tôi sử dụng các hàm INDEX và MATCH cùng nhau như một giải pháp thay thế cho VLOOKUP để giải quyết vấn đề này.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Sử dụng hàm XLOOKUP (có sẵn trong Excel 365, Excel Phiên bản 2021 và mới hơn)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

Lý do 2: Không tìm thấy giá trị tra cứu trong cột tra cứu (khớp chính xác)

Một trong những lý do phổ biến nhất khiến hàm VLOOKUP trả về lỗi #N/A là do không tìm thấy giá trị bạn đang tìm kiếm.

Như trong ví dụ bên dưới, chúng ta sẽ tìm tên dựa trên số điểm đã cho là 98 trong E6. Tuy nhiên, điểm số này không tồn tại trong cột đầu tiên của phạm vi dữ liệu, vì vậy hàm VLOOKUP trả về kết quả lỗi #N/A.

Giải pháp

Để khắc phục lỗi này, bạn có thể thử một trong các giải pháp sau.

• Nếu bạn muốn VLOOKUP tìm kiếm giá trị lớn nhất tiếp theo nhỏ hơn giá trị tra cứu, hãy thay đổi đối số cuối cùng KHÔNG ĐÚNG (đối sánh chính xác) với TRUE (tương đối khớp). Để biết thêm thông tin, vui lòng xem Ví dụ 1: Đối sánh chính xác với đối sánh gần đúng bằng VLOOKUP.
• Để tránh thay đổi đối số cuối cùng và nhận lời nhắc trong trường hợp không tìm thấy giá trị tra cứu, bạn có thể đặt hàm VLOOKUP bên trong hàm IFERROR:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Lý do 3: Giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong cột tra cứu (đối sánh gần đúng)

Như được hiển thị trong ảnh chụp màn hình bên dưới, bạn đang thực hiện tra cứu đối sánh gần đúng. Giá trị bạn đang tìm kiếm (ID số 1001 trong trường hợp này) nhỏ hơn giá trị nhỏ nhất 1002 trong cột tra cứu, do đó, hàm VLOOKUP trả về lỗi #N/A.

Giải pháp

Đây là hai giải pháp cho bạn.

• Đảm bảo rằng giá trị tra cứu lớn hơn hoặc bằng giá trị nhỏ nhất trong cột tra cứu.
• Nếu bạn muốn Excel để nhắc bạn rằng không tìm thấy giá trị tra cứu, chỉ cần lồng hàm VLOOKUP vào hàm IFERROR như sau:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Lý do 4: Các số được định dạng dưới dạng văn bản

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, kết quả lỗi #N/A trong ví dụ này là do kiểu dữ liệu không khớp giữa ô tra cứu (G6) và cột tra cứu (B6:B12) của bảng gốc. Ở đây, giá trị trong G6 là một số và các giá trị trong phạm vi B6:B12 là các số được định dạng dưới dạng văn bản.

Giải pháp

Để giải quyết vấn đề này, bạn cần chuyển đổi giá trị tra cứu về số. Đây là hai phương pháp dành cho bạn.

• Áp dụng tính năng Convert to Number
  
  Bấm vào ô muốn chuyển chữ sang số, chọn nút này  bên cạnh ô rồi chọn Chuyển đổi sang số.
  
• Áp dụng một công cụ tiện dụng để chuyển đổi hàng loạt giữa văn bản và số
  
  Mô hình Chuyển đổi giữa Văn bản và Số tính năng của Kutools for Excel giúp bạn dễ dàng chuyển đổi một dãy ô từ văn bản sang số và ngược lại. Nhận bản dùng thử miễn phí đầy đủ tính năng trong 30 ngày ngay bây giờ!
  

Lý do 5: Table_array không cố định khi kéo công thức VLOOKUP sang các ô khác

Như được hiển thị trong ảnh chụp màn hình bên dưới, có hai giá trị tra cứu trong E6 và E7. Sau khi nhận được kết quả đầu tiên ở ô F6, hãy kéo công thức VLOOKUP từ ô F6 sang ô F7, kết quả trả về lỗi #N/A. Đó là bởi vì các tham chiếu ô (B6:C12) theo mặc định là tương đối và được điều chỉnh khi bạn di chuyển xuống qua các hàng. Mảng bảng đã được chuyển xuống B7:C13, không còn chứa điểm tra cứu 73 nữa.

Dung dịch

Bạn cần khóa mảng bảng để giữ cho nó không đổi bằng cách thêm một $ ký trước các hàng và cột trong tham chiếu ô. Để biết thêm về tham chiếu tuyệt đối trong Excel, hãy xem hướng dẫn này: Excel tham chiếu tuyệt đối (cách tạo và sử dụng).

Lỗi #VALUE được trả về

Các điều kiện sau đây có thể khiến hàm VLOOKUP trả về kết quả lỗi #VALUE.

Lý do 1: Giá trị tra cứu vượt quá 255 ký tự

Như được hiển thị trong ảnh chụp màn hình bên dưới, giá trị tra cứu trong ô H4 vượt quá 255 ký tự, do đó hàm VLOOKUP trả về kết quả lỗi #VALUE.

Giải pháp

Để khắc phục giới hạn này, bạn có thể áp dụng một hàm tra cứu khác có thể xử lý các chuỗi dài hơn. Hãy thử một trong các công thức sau.

• CHỈ SỐ và MATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• Hàm XLOOKUP (có sẵn trong Excel 365, Excel Phiên bản 2021 và mới hơn):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

Lý do 2: Đối số col_index nhỏ hơn 1

Chỉ mục cột xác định số cột trong mảng bảng chứa giá trị mà bạn muốn trả về. Đối số này phải là một số dương tương ứng với một cột hợp lệ trong mảng bảng.

Nếu bạn nhập chỉ số cột nhỏ hơn 1 (nghĩa là không hoặc âm), VLOOKUP sẽ không thể xác định vị trí cột trong mảng bảng.

Dung dịch

Để khắc phục sự cố này, hãy đảm bảo rằng đối số chỉ mục cột trong công thức VLOOKUP của bạn là một số dương tương ứng với một cột hợp lệ trong mảng bảng.

Lỗi #REF được trả về

Phần này liệt kê một lý do khiến VLOOKUP trả về lỗi #REF và cung cấp các giải pháp cho vấn đề này.

Lý do: Đối số col_index lớn hơn số cột

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, mảng bảng chỉ có 4 cột. Tuy nhiên, chỉ số cột bạn đã chỉ định trong công thức VLOOKUP là 5, lớn hơn số lượng cột trong mảng bảng. Do đó, VLOOKUP sẽ không thể định vị cột và cuối cùng sẽ trả về lỗi #REF.

Giải pháp

• Chỉ định một số cột chính xác
  Đảm bảo rằng đối số chỉ mục cột trong công thức VLOOKUP của bạn là một số tương ứng với một cột hợp lệ trong mảng bảng.
• Tự động lấy số cột dựa trên tiêu đề cột đã chỉ định
  Nếu bảng chứa nhiều cột, bạn có thể gặp khó khăn khi xác định số chỉ mục cột chính xác. Tại đây, bạn có thể lồng hàm MATCH vào hàm VLOOKUP để tìm vị trí của cột dựa trên tiêu đề cột chắc chắn.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Chú thích: Trong công thức trên, MATCH("Email",B5:E5, 0) được sử dụng để lấy số cột của "E-mail" trong phạm vi ngày B6:E12. Ở đây kết quả là 4, được sử dụng làm col_index trong hàm VLOOKUP.

Giá trị không chính xác được trả về

Nếu bạn thấy VLOOKUP không trả về kết quả chính xác thì có thể do các nguyên nhân sau

Lý do 1: Cột tra cứu không được sắp xếp theo thứ tự tăng dần

Nếu bạn đã đặt đối số cuối cùng thành TRUE (Hoặc để trống) đối với kết quả khớp gần đúng và cột tra cứu không được sắp xếp theo thứ tự tăng dần, giá trị kết quả có thể không chính xác.

Dung dịch

Sắp xếp cột tra cứu theo thứ tự tăng dần có thể giúp bạn giải quyết vấn đề này. Để làm điều này, vui lòng làm theo các bước dưới đây:

1. Chọn các ô dữ liệu trong cột tra cứu, chuyển đến Ngày tab, nhấp vào Sắp xếp Nhỏ nhất đến Lớn nhất trong Sắp xếp & Lọc nhóm.
2. Trong tạp chí Cảnh báo sắp xếp hộp thoại, chọn Mở rộng sự lựa chọn và nhấp vào OK.

Lý do 2: Một cột được chèn hoặc xóa

Như thể hiện trong ảnh chụp màn hình bên dưới, giá trị ban đầu tôi muốn trả về nằm ở cột thứ tư của mảng bảng, vì vậy tôi chỉ định số col_index là 4. Khi một cột mới được chèn vào, cột kết quả sẽ trở thành cột thứ năm của bảng mảng, khiến VLOOKUP trả về kết quả từ một cột sai.

Giải pháp

Đây là hai giải pháp cho bạn.

• Bạn có thể thay đổi số chỉ mục cột theo cách thủ công để khớp với vị trí của cột trả về. Công thức ở đây nên được thay đổi thành:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Nếu bạn luôn muốn trả về kết quả từ một cột chắc chắn, chẳng hạn như cột Email trong ví dụ này. Công thức sau đây có thể giúp tự động so khớp chỉ mục cột dựa trên tiêu đề cột đã cho, bất kể cột được chèn hay xóa khỏi mảng bảng.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Ghi chú chức năng khác

• VLOOKUP chỉ tìm kiếm giá trị từ trái sang phải.
  Giá trị tra cứu nằm ở cột ngoài cùng bên trái và giá trị kết quả phải ở bất kỳ cột nào bên phải cột tra cứu.
• Nếu bạn để trống đối số cuối cùng, VLOOKUP sẽ sử dụng đối sánh gần đúng theo mặc định.
• VLOOKUP thực hiện tra cứu không phân biệt chữ hoa chữ thường.
• Đối với nhiều kết quả khớp, VLOOKUP chỉ trả về kết quả khớp đầu tiên mà nó tìm thấy trong mảng bảng, dựa trên thứ tự của các hàng trong mảng bảng.

Bài viết liên quan