Hàm VLOOKUP trong Excel là gì? Hướng dẫn sử dụng chi tiết và ví dụ

Hàm VLOOKUP là một trong những công cụ mạnh mẽ và hữu ích nhất trong Excel, giúp người dùng dò tìm và trích xuất dữ liệu một cách nhanh chóng và hiệu quả. Mặc dù được sử dụng phổ biến, nhiều người dùng Excel lâu năm vẫn chưa nắm vững hết các khía cạnh của hàm này. Bài viết này sẽ cung cấp cái nhìn sâu sắc về hàm VLOOKUP, giải thích cách hoạt động, cú pháp, các ví dụ minh họa cụ thể và những lỗi thường gặp, giúp bạn khai thác tối đa tiềm năng của nó trong công việc. Hàm VLOOKUP trong Excel là gì sẽ được làm rõ ngay dưới đây.

Phiên bản Excel được sử dụng trong bài viết này là Excel 2016, tuy nhiên, các hướng dẫn và ví dụ hoàn toàn có thể áp dụng cho các phiên bản Excel khác như 2007, 2010, 2013, 2019, 2021 và Microsoft Excel 365.

Hàm VLOOKUP là gì?

Hàm VLOOKUP (Vertical Lookup) trong Excel được thiết kế để tìm kiếm một giá trị trong cột đầu tiên của một bảng dữ liệu và trả về một giá trị tương ứng từ một cột khác trong cùng một hàng. “V” trong VLOOKUP là viết tắt của “Vertical”, ám chỉ việc tìm kiếm theo chiều dọc của bảng. Chức năng chính của hàm này là dò tìm dữ liệu theo cột và trả về kết quả theo hàng ngang.

Trong thực tế, VLOOKUP vô cùng hữu ích khi bạn cần tra cứu thông tin như tên sản phẩm, đơn giá, số lượng dựa trên mã sản phẩm, hoặc tìm tên nhân viên, xếp loại nhân viên dựa trên các mã định danh. Ngược lại, nếu bạn cần dò tìm dữ liệu theo hàng ngang và trả về kết quả theo cột dọc, bạn sẽ sử dụng hàm HLOOKUP (Horizontal Lookup).

Minh họa hàm VLOOKUP là một hàm hữu ích của Excel

Hàm VLOOKUP là một công cụ đắc lực trong Excel.

Công thức hàm VLOOKUP

Cú pháp chuẩn của hàm VLOOKUP như sau:

=VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)

Trong đó:

• Lookup_value (Giá trị dò tìm): Đây là giá trị bạn muốn tìm kiếm trong cột đầu tiên của bảng dữ liệu. Bạn có thể nhập trực tiếp giá trị hoặc tham chiếu đến một ô chứa giá trị đó.
• Table_array (Bảng giới hạn dò tìm): Là phạm vi của bảng dữ liệu mà bạn muốn hàm VLOOKUP tìm kiếm. Bảng này bao gồm cả cột chứa giá trị dò tìm và cột chứa kết quả trả về.
• Col_index_num (Số thứ tự cột lấy dữ liệu): Là số thứ tự của cột trong Table_array mà bạn muốn trả về giá trị. Cột đầu tiên trong Table_array được tính là 1, cột thứ hai là 2, và cứ thế tiếp tục.
• Range_lookup (Tìm kiếm tương đối hoặc chính xác):
  • Nếu đặt là TRUE hoặc 1 (hoặc bỏ trống): Hàm thực hiện tìm kiếm tương đối. Điều này có nghĩa là nếu không tìm thấy giá trị dò tìm chính xác, hàm sẽ trả về giá trị gần đúng nhất nhỏ hơn hoặc bằng giá trị dò tìm. Lưu ý: Khi sử dụng tìm kiếm tương đối, cột đầu tiên của Table_array phải được sắp xếp theo thứ tự tăng dần.
  • Nếu đặt là FALSE hoặc 0: Hàm thực hiện tìm kiếm chính xác. Hàm sẽ chỉ trả về giá trị nếu tìm thấy kết quả khớp hoàn toàn với Lookup_value. Nếu không tìm thấy, hàm sẽ trả về lỗi #N/A.

Khi bạn sao chép công thức sang các ô khác, để cố định phạm vi Table_array (ví dụ: $H$6:$J$13), bạn có thể sử dụng phím F4 sau khi chọn bảng hoặc thêm trực tiếp dấu $ vào trước ký tự cột và số hàng.

Công thức hàm VLOOKUP trong Excel

Công thức hàm VLOOKUP bao gồm 4 đối số quan trọng.

Ví dụ minh họa cách sử dụng hàm VLOOKUP

Để hiểu rõ hơn về cách áp dụng VLOOKUP, chúng ta hãy cùng xem xét các ví dụ thực tế sau:

Ví dụ 1: Tính phụ cấp theo chức vụ

Giả sử bạn có một danh sách nhân viên với chức vụ của họ ở Bảng 1, và một bảng phụ cấp theo chức vụ ở Bảng 2. Bạn muốn xác định mức phụ cấp tương ứng cho mỗi nhân viên.

• Bảng 1: Danh sách nhân viên (Cột D: Chức vụ)
• Bảng 2: Phụ cấp theo chức vụ (Cột B: Chức vụ, Cột C: Phụ cấp)

Tại ô E4 (tương ứng với nhân viên đầu tiên), bạn nhập công thức:
=VLOOKUP(D4,$B$16:$C$21,2,0)

Trong công thức này:

• D4 là chức vụ của nhân viên cần dò tìm.
• $B$16:$C$21 là phạm vi bảng phụ cấp, đã được cố định bằng dấu $.
• 2 chỉ ra rằng chúng ta muốn lấy dữ liệu từ cột thứ hai trong bảng phụ cấp (cột Phụ cấp).
• 0 (FALSE) yêu cầu tìm kiếm chính xác chức vụ.

Sau khi nhập công thức, bạn có thể sao chép nó xuống các ô còn lại để tính phụ cấp cho toàn bộ nhân viên.

Sử dụng hàm VLOOKUP để tính phụ cấp theo chức vụ

Việc áp dụng VLOOKUP giúp tự động hóa quá trình tính toán phụ cấp.

Ví dụ 2: Xếp loại học sinh theo điểm số

Tương tự, bạn có kết quả học tập của học sinh (Bảng 1) và một bảng quy định xếp loại theo điểm số (Bảng 2).

• Bảng 1: Danh sách học sinh (Cột D: Điểm số)
• Bảng 2: Xếp loại học sinh (Cột B: Điểm tối thiểu, Cột C: Xếp loại)

Tại ô E4, bạn nhập công thức:
=VLOOKUP(D4,$B$11:$C$15,2,1)

Trong ví dụ này:

• D4 là điểm số của học sinh.
• $B$11:$C$15 là phạm vi bảng xếp loại.
• 2 chỉ ra chúng ta muốn lấy kết quả xếp loại (cột thứ hai).
• 1 (TRUE) yêu cầu tìm kiếm tương đối, tức là tìm điểm số gần nhất mà nhỏ hơn hoặc bằng điểm số của học sinh. Quan trọng: Bảng B11:C15 phải được sắp xếp theo thứ tự tăng dần của cột điểm số.

Sử dụng hàm VLOOKUP để xếp loại học sinh

Hàm VLOOKUP giúp tự động xếp loại học sinh dựa trên điểm số.

Ví dụ 3: Tìm kiếm gần đúng với VLOOKUP

Khi bạn cần xếp loại học sinh dựa trên các khoảng điểm (ví dụ: 9-10 là Giỏi, 6.5-8.9 là Khá), bạn sẽ sử dụng Range_lookup là TRUE (hoặc 1). Điều này yêu cầu bảng tra cứu phải được sắp xếp tăng dần theo cột đầu tiên.

Giả sử bạn có các khoảng xếp loại như sau:

• Từ 9 trở lên: Giỏi
• Từ 6.5 đến dưới 9: Khá
• Từ 5 đến dưới 6.5: Trung bình

Công thức áp dụng sẽ là:
=VLOOKUP(Điểm_của_học_sinh,$B$10:$C$12,2,TRUE)

Minh họa tìm kiếm gần đúng bằng VLOOKUP

Tìm kiếm gần đúng hữu ích khi phân loại theo các khoảng giá trị.

Lưu ý quan trọng: Khi sử dụng Range_lookup = TRUE, bạn phải đảm bảo cột đầu tiên của bảng tra cứu (Table_array) được sắp xếp theo thứ tự từ nhỏ đến lớn.

Ví dụ 4: Kết hợp VLOOKUP với Data Validation

Bạn có thể kết hợp VLOOKUP với Data Validation để tạo ra một danh sách thả xuống, khi chọn một mục trong danh sách, VLOOKUP sẽ tự động hiển thị thông tin liên quan.

1. Thiết lập Data Validation: Chọn ô bạn muốn hiển thị, vào tab Data > Data Validation. Tại Allow, chọn List, và tại Source, chọn phạm vi dữ liệu bạn muốn hiển thị trong danh sách thả xuống.
2. Nhập công thức VLOOKUP: Tại ô bạn muốn hiển thị kết quả, nhập công thức VLOOKUP tham chiếu đến ô chứa Data Validation. Ví dụ: =VLOOKUP(Ô_chọn_trong_Data_Validation, Bảng_dữ_liệu, Số_cột_kết_quả, 0).

Sử dụng Data Validation kết hợp VLOOKUP

Sự kết hợp này giúp tạo ra các biểu mẫu tương tác hiệu quả.

Ví dụ 5: Tìm mã ngành học với VLOOKUP và LEFT

Để tìm tên ngành học dựa trên một phần của số báo danh (ví dụ: ký tự đầu tiên), bạn có thể kết hợp VLOOKUP với hàm LEFT.

Giả sử số báo danh nằm ở ô C10 và bảng mã ngành có dạng: Ký tự đầu | Tên ngành (ở F4:G5).

Công thức sẽ là:
=VLOOKUP(LEFT(C10,1),$F$4:$G$5,2,0)

• LEFT(C10,1): Lấy ký tự đầu tiên từ ô C10.
• $F$4:$G$5: Bảng mã ngành đã cố định.
• 2: Lấy tên ngành từ cột thứ hai.
• 0: Tìm kiếm chính xác.

Kết hợp VLOOKUP và LEFT để tìm mã ngành

Hàm LEFT giúp trích xuất ký tự cần thiết cho việc dò tìm.

Các lỗi thường gặp khi sử dụng hàm VLOOKUP

Lỗi #N/A

Lỗi này xảy ra khi VLOOKUP không tìm thấy giá trị dò tìm (Lookup_value) trong cột đầu tiên của Table_array. Nguyên nhân có thể do:

• Sai phạm vi Table_array: VLOOKUP chỉ tìm kiếm trong cột đầu tiên của phạm vi bạn cung cấp. Nếu giá trị cần tìm nằm ở cột khác và không phải là cột đầu tiên, bạn sẽ gặp lỗi này. Giải pháp là điều chỉnh lại Table_array hoặc sử dụng các hàm khác như INDEX/MATCH.
• Dữ liệu không tồn tại: Giá trị bạn tìm kiếm thực sự không có trong bảng dữ liệu.
• Khoảng trắng ẩn hoặc ký tự không in: Dữ liệu trong ô có thể chứa các ký tự không hiển thị hoặc khoảng trắng thừa.
• Định dạng dữ liệu không khớp: Ví dụ, một bên là số, một bên là văn bản.

Bạn có thể khắc phục lỗi này bằng cách sử dụng hàm IFERROR để hiển thị một thông báo khác thay vì #N/A.

Lỗi #N/A khi không tìm thấy dữ liệu

Lỗi #N/A cho biết giá trị dò tìm không được tìm thấy.

Lỗi #REF!

Lỗi này xuất hiện khi Col_index_num (số thứ tự cột lấy dữ liệu) lớn hơn tổng số cột trong Table_array. Hãy kiểm tra lại giá trị này để đảm bảo nó nằm trong phạm vi số cột của bảng.

Lỗi #REF! do sai thứ tự cột

Kiểm tra Col_index_num để tránh lỗi #REF!.

Lỗi #VALUE!

Lỗi này xảy ra nếu Col_index_num nhỏ hơn 1. Cột đầu tiên trong Table_array luôn được đánh số là 1.

Lỗi #VALUE! do Col_index_num nhỏ hơn 1

Đảm bảo Col_index_num luôn lớn hơn hoặc bằng 1.

Lỗi #NAME?

Lỗi này thường xuất hiện khi giá trị văn bản trong Lookup_value không được đặt trong dấu ngoặc kép ("). Ví dụ, thay vì "Apple", bạn gõ Apple.

Lỗi #NAME? do thiếu dấu ngoặc kép

Luôn dùng dấu ngoặc kép cho các giá trị văn bản.

Một số lưu ý khi sử dụng hàm VLOOKUP

Sử dụng tham chiếu tuyệt đối

Khi sao chép công thức VLOOKUP, hãy đảm bảo Table_array được cố định bằng dấu $ (sử dụng phím F4). Điều này ngăn bảng tham chiếu bị dịch chuyển theo, đảm bảo kết quả luôn chính xác.

Tham chiếu tuyệt đối là yếu tố then chốt để sao chép công thức đúng.

Tránh lưu trữ số dưới dạng văn bản

Nếu bảng tra cứu chứa dữ liệu số được định dạng dưới dạng văn bản, trong khi giá trị dò tìm lại là số, VLOOKUP có thể trả về lỗi #N/A. Hãy đảm bảo định dạng dữ liệu nhất quán giữa giá trị dò tìm và bảng tra cứu.

Tránh định dạng số dưới dạng văn bản trong bảng tra cứu

Chuyển đổi định dạng dữ liệu nếu cần thiết.

Xử lý giá trị trùng lặp trong bảng tra cứu

Nếu bảng tra cứu có các giá trị bị trùng ở cột đầu tiên, VLOOKUP sẽ chỉ trả về kết quả của lần trùng lặp đầu tiên mà nó tìm thấy. Để giải quyết vấn đề này:

• Sử dụng “Remove Duplicates”: Chọn bảng dữ liệu, vào tab Data > Remove Duplicates.
• Sử dụng Pivot Table: Lọc ra danh sách duy nhất các kết quả.

Sử dụng chức năng Remove Duplicates

Loại bỏ các giá trị trùng lặp để đảm bảo tính duy nhất của kết quả.

Một số hạn chế của hàm VLOOKUP

• Chỉ dò tìm từ trái sang phải: VLOOKUP không thể tìm kiếm giá trị ở cột bên trái của cột dò tìm. Trong trường hợp này, bạn cần kết hợp với các hàm khác như INDEX và MATCH.
• Hiệu quả với giá trị riêng biệt: Hàm hoạt động tốt nhất với các giá trị duy nhất. Khi có nhiều giá trị trùng lặp, nó chỉ trả về kết quả đầu tiên.
• Số thứ tự cột cố định: Col_index_num phải được nhập thủ công, gây khó khăn khi bảng dữ liệu thay đổi cấu trúc. Hàm MATCH có thể giúp khắc phục hạn chế này bằng cách tự động xác định số thứ tự cột.
• Mặc định là tìm kiếm tương đối: Nếu không chỉ định rõ Range_lookup, VLOOKUP sẽ mặc định là TRUE (tương đối), có thể dẫn đến kết quả sai lệch nếu bảng không được sắp xếp đúng cách.
• Ảnh hưởng đến hiệu suất: Với các bảng dữ liệu rất lớn, việc sử dụng nhiều hàm VLOOKUP có thể làm chậm tốc độ xử lý của Excel.

Cách sử dụng hàm VLOOKUP trong Excel tuy đơn giản nhưng đòi hỏi sự cẩn trọng trong việc thiết lập các tham số và xử lý các trường hợp đặc biệt. Bằng cách hiểu rõ cú pháp, các ví dụ và những lưu ý trên, bạn có thể tối ưu hóa việc sử dụng hàm VLOOKUP cho công việc của mình.