Hàm VLOOKUP là một trong những công cụ mạnh mẽ và hữu ích nhất trong Microsoft Excel, cho phép người dùng dò tìm và trả về dữ liệu một cách hiệu quả. Tuy nhiên, không phải ai cũng nắm vững cách sử dụng hàm này, ngay cả những người đã làm việc với Excel trong thời gian dài. Bài viết này sẽ đi sâu vào Công Thức Vlookup Excel, cung cấp cái nhìn toàn diện và chi tiết nhất, giúp bạn khai thác tối đa sức mạnh của hàm.
Microsoft Excel, với sự phát triển không ngừng, cung cấp nhiều phiên bản khác nhau như 2007, 2010, 2013, 2016, 2019, 2021 và Microsoft Excel 365. Các ví dụ trong bài viết này được thực hiện trên phiên bản Excel 2016, nhưng bạn hoàn toàn có thể áp dụng tương tự cho các phiên bản khác.
1. Hàm VLOOKUP là gì?
Nội dung
Hàm VLOOKUP (Vertical Lookup) được thiết kế để tìm kiếm dữ liệu theo chiều dọc trong một bảng hoặc phạm vi dữ liệu. Khi bạn cần trích xuất một thông tin cụ thể dựa trên một giá trị đã biết, VLOOKUP sẽ giúp bạn tìm đúng giá trị đó trong cột đầu tiên của bảng và trả về dữ liệu tương ứng từ một cột khác trong cùng một hàng.
Trong thực tế, hàm VLOOKUP vô cùng hữu dụng cho các tác vụ như:
- Tìm tên sản phẩm, đơn giá, hoặc số lượng dựa trên mã sản phẩm hoặc mã vạch.
- Tra cứu thông tin nhân viên, xếp loại nhân viên dựa trên mã số nhân viên hoặc các tiêu chí khác.
Ngược lại, nếu bạn cần tìm kiếm dữ liệu theo chiều ngang, hàm HLOOKUP sẽ là lựa chọn phù hợp. Tên gọi VLOOKUP bắt nguồn từ “Vertical” (dọc) và “Look Up” (tìm kiếm).
Minh họa hàm VLOOKUP là gì
2. Công thức hàm VLOOKUP
Cú pháp cơ bản của hàm VLOOKUP như sau:
=VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)
Trong đó:
- Lookup_value: Đây là giá trị bạn muốn tìm kiếm. 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: Là bảng hoặc phạm vi dữ liệu mà bạn muốn dò tìm. VLOOKUP sẽ tìm
Lookup_valuetrong cột đầu tiên củaTable_array. - Col_index_num: Là số thứ tự của cột trong
Table_arraymà bạn muốn lấy dữ liệu trả về. Cột đầu tiên trongTable_arrayđược tính là 1, cột thứ hai là 2, và cứ thế tiếp tục. - Range_lookup: Là một giá trị logic (TRUE hoặc FALSE) hoặc 1 hoặc 0, xác định kiểu dò tìm:
TRUE(hoặc 1, hoặc bỏ trống): Tìm kiếm tương đối. Hàm sẽ trả về giá trị gần đúng nhất nếu không tìm thấy giá trị chính xác. Yêu cầu cột đầu tiên củaTable_arrayphải được sắp xếp theo thứ tự tăng dần.FALSE(hoặc 0): Tìm kiếm chính xác. Hàm chỉ trả về kết quả nếu tìm thấy giá trị trùng khớp hoàn toàn. Đây là tùy chọn được sử dụng phổ biến nhất.
Khi bạn sao chép công thức VLOOKUP sang các ô khác, để giữ nguyên phạm vi dò tìm (Table_array), bạn cần cố định nó bằng cách sử dụng dấu $ (ví dụ: $B$16:$C$21) hoặc nhấn phím F4 sau khi chọn phạm vi.
Công thức hàm VLOOKUP trong Excel
3. Ví dụ thực tế về hàm VLOOKUP
Để hiểu rõ hơn, chúng ta sẽ đi vào các ví dụ cụ thể:
Ví dụ 1: Tính phụ cấp theo chức vụ
Giả sử bạn có danh sách nhân viên với chức vụ của họ ở Bảng 1, và Bảng 2 chứa mức phụ cấp tương ứng với từng chức vụ. Bạn muốn điền mức phụ cấp vào Bảng 1.
Tại ô E4 (chức vụ của nhân viên đầu tiên), bạn sử dụng công thức:=VLOOKUP(D4,$B$16:$C$21,2,0)
Trong đó:
D4là chức vụ của nhân viên cần dò tìm.$B$16:$C$21là bảng tra cứu chức vụ và phụ cấp, đã được cố định bằng dấu$.2là số thứ tự của cột chứa thông tin phụ cấp trong bảng tra cứu (cột đầu là 1, cột phụ cấp là 2).0là yêu cầu dò tìm chính xác.
Sau khi nhập công thức, bạn có thể sao chép nó cho các nhân viên còn lại.
Tính phụ cấp theo chức vụ bằng VLOOKUP
Ví dụ 2: Xếp loại học sinh theo điểm số
Tương tự, với bảng điểm của học sinh và bảng xếp loại theo điểm, bạn có thể dùng VLOOKUP để tự động xếp loại.
Tại ô E4 (điểm của học sinh đầu tiên), bạn dùng công thức:=VLOOKUP(D4,$B$11:$C$15,2,1)
Điểm khác biệt ở đây là Range_lookup là 1 (TRUE), cho phép dò tìm tương đối. Excel sẽ tìm giá trị điểm gần nhất với điểm của học sinh trong bảng xếp loại.
Xếp loại học sinh bằng VLOOKUP
Ví dụ 3: Tìm kiếm gần đúng
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ử dụng Range_lookup là TRUE (hoặc 1).
Công thức sẽ là: =VLOOKUP(C4,$B$10:$C$12,2,TRUE)
Lưu ý quan trọng: Với tìm kiếm tương đối, bảng tra cứu (Table_array) bắt buộc phải được sắp xếp theo thứ tự tăng dần của cột đầu tiên.
Tìm kiếm gần đúng với VLOOKUP
Ví dụ 4: VLOOKUP kết hợp Data Validation
Bạn có thể kết hợp VLOOKUP với Data Validation để tạo danh sách thả xuống, giúp việc nhập liệu và tra cứu trở nên thuận tiện hơn. Sau khi thiết lập danh sách thả xuống, bạn dùng VLOOKUP để lấy thông tin tương ứng.
Ví dụ, nếu ô C10 là danh sách thả xuống, bạn có thể dùng:=VLOOKUP(C10,B4:D7,2,0) và =VLOOKUP(C10,B4:D7,3,0) để lấy dữ liệu từ cột thứ 2 và thứ 3.
Kết hợp VLOOKUP và Data Validation
Ví dụ 5: Tìm mã ngành học
Khi mã ngành học được mã hóa dựa trên ký tự đầu tiên của số báo danh, bạn có thể kết hợp hàm VLOOKUP với hàm LEFT để trích xuất ký tự và thực hiện tra cứu.
Công thức ví dụ: =VLOOKUP(LEFT(C10),F4:G5,2,0)
Hàm LEFT(C10) sẽ lấy ký tự đầu tiên của số báo danh trong ô C10, sau đó VLOOKUP sẽ tìm ký tự này trong bảng mã ngành (F4:G5) và trả về tên ngành tương ứng.
Kết hợp VLOOKUP và LEFT để tìm mã ngành
4. Các lỗi thường gặp khi dùng hàm VLOOKUP và cách khắc phục
Lỗi #N/A
Lỗi này xảy ra khi:
Lookup_valuekhông tồn tại trong cột đầu tiên củaTable_array(khi dò tìm chính xác –Range_lookup = 0).Lookup_valuekhông nằm trong khoảng giá trị của cột đầu tiên trongTable_array(khi dò tìm tương đối –Range_lookup = 1và cột chưa sắp xếp).- Cột đầu tiên của
Table_arraykhông chứaLookup_value.
Khắc phục:
- Kiểm tra lại
Lookup_valuevàTable_arrayđể đảm bảo chúng khớp nhau. - Nếu cần dò tìm từ phải sang trái, hãy xem xét sử dụng hàm
INDEXkết hợpMATCH. - Sử dụng hàm
IFERRORđể hiển thị một thông báo tùy chỉnh thay vì lỗi #N/A. - Đảm bảo không có khoảng trắng thừa hoặc sai định dạng dữ liệu trong các ô.
Lỗi #N/A trong VLOOKUP
Lỗi #REF!
Lỗi này xuất hiện khi Col_index_num lớn hơn tổng số cột trong Table_array.
Khắc phục: Đảm bảo Col_index_num nhỏ hơn hoặc bằng số cột trong Table_array.
Lỗi #REF! trong VLOOKUP
Lỗi #VALUE!
Lỗi này xảy ra khi Col_index_num nhỏ hơn 1.
Khắc phục: Đảm bảo Col_index_num lớn hơn hoặc bằng 1.
Lỗi #VALUE! trong VLOOKUP
Lỗi #NAME?
Lỗi này thường xuất hiện khi bạn nhập giá trị dạng văn bản cho Lookup_value mà quên đặt nó trong dấu ngoặc kép (").
Khắc phục: Đặt giá trị văn bản trong dấu ngoặc kép, ví dụ: "Cải xoăn".
Lỗi #NAME? trong VLOOKUP
5. Một số lưu ý quan trọng khi sử dụng hàm VLOOKUP
Sử dụng tham chiếu tuyệt đối ($)
Khi sao chép công thức, hãy luôn cố định Table_array bằng dấu $ (ví dụ: $B$16:$C$21) hoặc phím F4. Điều này đảm bảo phạm vi dò tìm không bị thay đổi, giữ cho kết quả luôn chính xác. Nếu không, Table_array sẽ dịch chuyển tương ứng với ô chứa công thức, dẫn đến sai lệch dữ liệu.
Sử dụng tham chiếu tuyệt đối cho VLOOKUP
Định dạng dữ liệu
Đảm bảo rằng định dạng dữ liệu của Lookup_value và cột đầu tiên trong Table_array là nhất quán. Nếu dữ liệu số trong bảng tra cứu được lưu dưới dạng văn bản, VLOOKUP có thể trả về lỗi #N/A.
Kiểm tra định dạng dữ liệu cho VLOOKUP
Giá trị trùng lặp trong bảng tra cứu
Nếu Table_array chứa các giá trị trùng lặp ở cột đầu tiên, VLOOKUP sẽ chỉ trả về kết quả tương ứng với lần xuất hiện đầu tiên của giá trị đó.
Giải pháp:
- Sử dụng chức năng “Remove Duplicates” trên tab “Data” để loại bỏ các hàng trùng lặp.
- Sử dụng Pivot Table để lọc và tổng hợp kết quả.
Xử lý giá trị trùng lặp với VLOOKUP
6. Hạn chế của hàm VLOOKUP
Mặc dù mạnh mẽ, VLOOKUP cũng có một số hạn chế cần lưu ý:
- Chỉ dò tìm từ trái sang phải: VLOOKUP không thể tìm kiếm giá trị ở các cột bên trái của cột dò tìm. Để khắc phục, bạn có thể cần kết hợp
INDEXvàMATCH. - Kém hiệu quả với giá trị không riêng biệt: Khi có nhiều giá trị trùng khớp, VLOOKUP chỉ trả về giá trị đầu tiên.
- Số thứ tự cột cố định: Bạn phải nhập thủ công số thứ tự cột, điều này có thể gây bất tiện khi sao chép công thức. Hàm
MATCHcó thể giúp tự động hóa việc này. - Mặc định tìm kiếm tương đối: Nếu không chỉ định rõ
Range_lookuplàFALSE, hàm sẽ mặc định làTRUE, có thể dẫn đến kết quả không chính xác nếu cột chưa sắp xếp. - Ảnh hưởng hiệu năng: 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.
Hạn chế của VLOOKUP
Bằng cách hiểu rõ công thức VLOOKUP Excel, các ví dụ minh họa và các lưu ý quan trọng, bạn sẽ có thể sử dụng hàm này một cách hiệu quả, tiết kiệm thời gian và nâng cao năng suất làm việc trên Excel.
Trên đây là bài viết chia sẻ cho bạn hàm VLOOKUP trong Excel là gì và cách sử dụng hàm VLOOKUP chi tiết. Mong rằng những thông tin này đã giúp ích cho bạn trong việc hiểu và sử dụng hàm VLOOKUP cho công việc của mình nhé!
