Hàm Vlookup trong Excel là một trong những hàm phổ biến và quan trọng nhất mà bạn nên biết khi làm việc với bảng tính. Hàm này cho phép bạn tra cứu giá trị của một ô trong một bảng dữ liệu dựa trên giá trị của một ô khác. Biết cách sử dụng hàm Vlookup, bạn có thể tìm kiếm thông tin, so sánh dữ liệu, tính toán các công thức phức tạp và nhiều ứng dụng khác. Trong bài viết này, mình sẽ hướng dẫn bạn cách sử dụng hàm Vlookup trong Excel từ cơ bản đến nâng cao, cùng với các ví dụ minh họa và các lưu ý khi dùng hàm này.
Danh mục nội dung
- Cú pháp và ý nghĩa của các tham số của hàm Vlookup
- Cách sử dụng hàm Vlookup để tra cứu chính xác và tương đối
- Cách sử dụng hàm Vlookup kết hợp với các hàm khác như If, Match, Index, Left, Right…
- Cách sử dụng hàm Vlookup với nhiều điều kiện
- Cách sử dụng hàm Vlookup tra cứu ngang
- Cách sử dụng hàm Vlookup tra cứu ở file khác
- Cách sử dụng hàm Vlookup và ký tự đại diện
- Lỗi thường gặp khi dùng hàm Vlookup và cách khắc phục
- Kết luận
Cú pháp và ý nghĩa của các tham số của hàm Vlookup
Hàm Vlookup có cú pháp như sau:
=VLOOKUP (giá trị cần tra cứu, bảng dữ liệu, số thứ tự cột trả về, kiểu tra cứu)
Trong đó:
Giá trị cần tra cứu: là giá trị mà bạn muốn tìm kiếm trong bảng dữ liệu. Giá trị này có thể là một số, một chuỗi, một ô tham chiếu hoặc một công thức.
Bảng dữ liệu: là vùng dữ liệu mà bạn muốn tra cứu giá trị. Bảng dữ liệu phải có ít nhất hai cột, trong đó cột đầu tiên chứa giá trị cần tra cứu. Bạn nên sử dụng F4 để cố định bảng dữ liệu khi copy công thức.
Số thứ tự cột trả về: là số thứ tự của cột trong bảng dữ liệu mà bạn muốn lấy giá trị trả về. Số này phải là một số nguyên dương và không vượt quá số cột của bảng dữ liệu.
Kiểu tra cứu: là một giá trị logic (TRUE hoặc FALSE) quyết định kiểu tra cứu là chính xác hay tương đối. Nếu bạn nhập TRUE hoặc bỏ trống, hàm Vlookup sẽ tra cứu tương đối, tức là tìm kiếm giá trị gần nhất nhỏ hơn hoặc bằng giá trị cần tra cứu. Nếu bạn nhập FALSE, hàm Vlookup sẽ tra cứu chính xác, tức là chỉ tìm kiếm giá trị khớp hoàn toàn với giá trị cần tra cứu.
Ví dụ: Bạn có bảng dữ liệu sau:
| Mã sản phẩm | Tên sản phẩm | Đơn giá |
|---|---|---|
| SP001 | Bút bi | 5000 |
| SP002 | Bút chì | 3000 |
| SP003 | Bút lông | 7000 |
| SP004 | Bút máy | 10000 |
Bạn muốn tìm tên sản phẩm và đơn giá của sản phẩm có mã là SP003. Bạn có thể sử dụng hàm Vlookup như sau:
Tìm tên sản phẩm: =VLOOKUP(“SP003”,$A$2:$C$5,2,FALSE)
Tìm đơn giá: =VLOOKUP(“SP003”,$A$2:$C$5,3,FALSE)
Kết quả:
| Mã sản phẩm | Tên sản phẩm | Đơn giá |
|---|---|---|
| SP003 | Bút lông | 7000 |
Cách sử dụng hàm Vlookup để tra cứu chính xác và tương đối
Như đã nói ở trên, bạn có thể sử dụng hàm Vlookup để tra cứu chính xác hoặc tương đối bằng cách nhập giá trị TRUE hoặc FALSE cho tham số kiểu tra cứu.
Tra cứu chính xác (FALSE) có nghĩa là bạn chỉ muốn tìm kiếm những giá trị khớp hoàn toàn với giá trị cần tra cứu. Điều này thường được sử dụng khi bạn muốn tra cứu theo mã, số điện thoại, email hoặc các thông tin duy nhất.
Tra cứu tương đối (TRUE) có nghĩa là bạn muốn tìm kiếm những giá trị gần nhất nhỏ hơn hoặc bằng giá trị cần tra cứu. Điều này thường được sử dụng khi bạn muốn tra cứu theo các khoảng giá trị, như bảng điểm, bảng lương, bảng thuế…
Ví dụ: Bạn có bảng dữ liệu sau:
| Điểm | Xếp loại |
|---|---|
| 0-4 | Yếu |
| 5-6 | Trung bình |
| 7-8 | Khá |
| 9-10 | Giỏi |
Bạn muốn tìm xếp loại của học sinh có điểm là 8.5. Bạn có thể sử dụng hàm Vlookup như sau:
Xếp loại: =VLOOKUP(8.5,$A$2:$B$5,2,TRUE)
Kết quả:
| Điểm | Xếp loại |
|---|---|
| 8.5 | Khá |
Lưu ý: Khi sử dụng hàm Vlookup để tra cứu tương đối, bạn phải đảm bảo rằng cột đầu tiên của bảng dữ liệu được sắp xếp theo thứ tự tăng dần. Nếu không, hàm Vlookup sẽ không hoạt động chính xác.
Cách sử dụng hàm Vlookup kết hợp với các hàm khác như If, Match, Index, Left, Right…
Hàm Vlookup có thể được kết hợp với các hàm khác để tăng khả năng tra cứu và xử lý dữ liệu. Một số cách kết hợp phổ biến là:
Sử dụng hàm If để kiểm tra điều kiện trước khi tra cứu. Ví dụ: Bạn muốn tra cứu đơn giá của sản phẩm nếu mã sản phẩm không rỗng. Bạn có thể sử dụng công thức sau:
=IF(A2<>“”,VLOOKUP(A2,$D$2:$F$5,3,FALSE),“”)
Sử dụng hàm Match để thay thế cho số thứ tự cột trả về. Ví dụ: Bạn muốn tra cứu đơn giá của sản phẩm theo tên sản phẩm. Bạn có thể sử dụng công thức sau:
=VLOOKUP(B2,$D$2:$F$5,MATCH(“Đơn giá”,$D$1:$F$1,0),FALSE)
Sử dụng hàm Index để thay thế cho bảng dữ liệu. Ví dụ: Bạn muốn tra cứu đơn giá của sản phẩm theo mã sản phẩm và tên sản phẩm. Bạn có thể sử dụng công thức sau:
=VLOOKUP(A2,INDEX($D$2:$F$5,MATCH(B2,$D$1:$F$1,0)),3,FALSE)
Sử dụng hàm Left hoặc Right để lấy một phần của giá trị cần tra cứu. Ví dụ: Bạn muốn tra cứu tên sản phẩm theo mã sản phẩm, nhưng chỉ lấy hai ký tự đầu tiên của mã sản phẩm. Bạn có thể sử dụng công thức sau:
=VLOOKUP(LEFT(A2,2)&“*”,$D$2:$F$5,2,FALSE)
Cách sử dụng hàm Vlookup với nhiều điều kiện
Hàm Vlookup chỉ cho phép bạn tra cứu theo một điều kiện duy nhất, là giá trị của cột đầu tiên trong bảng dữ liệu. Nếu bạn muốn tra cứu theo nhiều điều kiện, bạn có thể sử dụng một trong các cách sau:
Tạo một cột mới trong bảng dữ liệu để kết hợp các điều kiện thành một giá trị duy nhất. Sau đó, sử dụng hàm Vlookup như bình thường với cột mới này làm cột đầu tiên. Ví dụ: Bạn muốn tra cứu đơn giá của sản phẩm theo mã sản phẩm và tên sản phẩm. Bạn có thể tạo một cột mới trong bảng dữ liệu với công thức:
=D2&“-”&E2
Sau đó, sử dụng hàm Vlookup như sau:
=VLOOKUP(A2&“-”&B2,$G$2:$H$5,2,FALSE)
Sử dụng hàm ArrayFormula để tạo một mảng tạm thời chứa các điều kiện. Sau đó, sử dụng hàm Vlookup với mảng này làm bảng dữ liệu. Ví dụ: Bạn muốn tra cứu đơn giá của sản phẩm theo mã sản phẩm và tên sản phẩm. Bạn có thể sử dụng công thức sau:
=VLOOKUP(A2&“-”&B2,ArrayFormula($D$2:$D$5&“-”&$E$2:$E$5&“|”&$F$2:$F$5),2,FALSE)
Lưu ý: Bạn phải nhấn Ctrl + Shift + Enter để nhập công thức này.
Sách học Excel
Cách sử dụng hàm Vlookup tra cứu ngang
Hàm Vlookup chỉ cho phép bạn tra cứu theo chiều dọc, tức là tìm kiếm giá trị trong cột đầu tiên của bảng dữ liệu và trả về giá trị trong cùng một hàng. Nếu bạn muốn tra cứu theo chiều ngang, tức là tìm kiếm giá trị trong hàng đầu tiên của bảng dữ liệu và trả về giá trị trong cùng một cột, bạn có thể sử dụng hàm Hlookup hoặc kết hợp hàm Vlookup với hàm Transpose.
Hàm Hlookup có cú pháp tương tự như hàm Vlookup, chỉ khác là bạn nhập số thứ tự hàng trả về thay vì số thứ tự cột trả về. Ví dụ: Bạn muốn tra cứu đơn giá của sản phẩm theo tên sản phẩm và năm. Bạn có bảng dữ liệu sau:
| Tên sản phẩm | 2020 | 2021 | 2022 |
|---|---|---|---|
| Bút bi | 5000 | 5500 | 6000 |
| Bút chì | 3000 | 3300 | 3600 |
| Bút lông | 7000 | 7700 | 8400 |
| Bút máy | 10000 | 11000 | 12000 |
Bạn có thể sử dụng hàm Hlookup như sau:
=HLOOKUP(A2,$D$1:$G$5,B2,FALSE)
Hoặc bạn có thể sử dụng hàm Vlookup kết hợp với hàm Transpose để đảo ngược bảng dữ liệu từ chiều ngang sang chiều dọc, rồi tra cứu như bình thường. Ví dụ:
=VLOOKUP(A2,TRANSPOSE($D$1:$G$5),B2,FALSE)
Cách sử dụng hàm Vlookup tra cứu ở file khác
Hàm Vlookup cho phép bạn tra cứu dữ liệu từ một file Excel khác, miễn là file đó được mở và có liên kết với file hiện tại. Để làm được điều này, bạn chỉ cần nhập đường dẫn của file khác vào tham số bảng dữ liệu của hàm Vlookup. Ví dụ: Bạn muốn tra cứu đơn giá của sản phẩm
theo mã sản phẩm từ file Excel khác có tên là “Danh sách sản phẩm.xlsx”. Bạn có thể sử dụng hàm Vlookup như sau:
=VLOOKUP(A2,‘[Danh sách sản phẩm.xlsx]Sheet1’!$A$2:$C$5,3,FALSE)
Lưu ý: Bạn phải đặt tên file và tên sheet trong dấu ngoặc vuông và dấu nháy đơn. Bạn cũng có thể chọn bảng dữ liệu từ file khác bằng cách nhấn F4 sau khi nhập giá trị cần tra cứu.
Cách sử dụng hàm Vlookup và ký tự đại diện
Hàm Vlookup cho phép bạn sử dụng các ký tự đại diện để tra cứu những giá trị có một phần không xác định hoặc có thể thay đổi. Có hai ký tự đại diện là:
Dấu sao (*) để thay thế cho một hoặc nhiều ký tự bất kỳ. Ví dụ: Bạn muốn tra cứu tên sản phẩm có chứa từ “bút”. Bạn có thể sử dụng công thức như sau:
=VLOOKUP(“bút”,$A$2:$C$5,2,FALSE)
Dấu hỏi chấm (?) để thay thế cho một ký tự bất kỳ. Ví dụ: Bạn muốn tra cứu tên sản phẩm có 6 ký tự, trong đó ký tự thứ 3 là “t”. Bạn có thể sử dụng công thức như sau:
=VLOOKUP(“??t???”,$A$2:$C$5,2,FALSE)
Lưu ý: Khi sử dụng ký tự đại diện, bạn phải nhập FALSE cho tham số kiểu tra cứu. Nếu bạn muốn tra cứu chính xác các ký tự *, ? hoặc ~, bạn phải thêm dấu ngã (~) trước chúng. Ví dụ: Bạn muốn tra cứu tên sản phẩm có chứa ký tự *. Bạn có thể sử dụng công thức như sau:
=VLOOKUP(“*~**”,$A$2:$C$5,2,FALSE)
Lỗi thường gặp khi dùng hàm Vlookup và cách khắc phục
Khi sử dụng hàm Vlookup, bạn có thể gặp một số lỗi phổ biến như sau:
Lỗi #N/A: có nghĩa là không tìm thấy giá trị cần tra cứu trong bảng dữ liệu. Để khắc phục, bạn nên kiểm tra lại giá trị cần tra cứu có đúng không, bảng dữ liệu có chứa giá trị đó không, kiểu tra cứu có phù hợp không, và bảng dữ liệu có được sắp xếp theo thứ tự tăng dần khi tra cứu tương đối không. Bạn cũng có thể sử dụng hàm IfError để trả về một giá trị khác khi gặp lỗi này. Ví dụ:
=IFERROR(VLOOKUP(A2,$D$2:$F$5,3,FALSE),“Không tìm thấy”)
Lỗi #REF!: có nghĩa là số thứ tự cột trả về hoặc số thứ tự hàng trả về không hợp lệ. Để khắc phục, bạn nên kiểm tra lại số này có lớn hơn 0 và nhỏ hơn hoặc bằng số cột hoặc số hàng của bảng dữ liệu không.
Lỗi #VALUE!: có nghĩa là giá trị cần tra cứu hoặc bảng dữ liệu không hợp lệ. Để khắc phục, bạn nên kiểm tra lại giá trị cần tra cứu có phải là một số, một chuỗi, một ô tham chiếu hoặc một công thức không, và bảng dữ liệu có phải là một vùng dữ liệu hợp lệ không.
Lỗi #NAME?: có nghĩa là tên hàm hoặc tên file không được nhận dạng. Để khắc phục, bạn nên kiểm tra lại chính tả của tên hàm hoặc tên file, và đảm bảo rằng file được liên kết với file hiện tại.
Kết luận
Hàm Vlookup trong Excel là một công cụ mạnh mẽ để tra cứu và xử lý dữ liệu trong bảng tính. Bạn có thể sử dụng hàm này để tra cứu chính xác hoặc tương đối, kết hợp với các hàm khác, tra cứu theo nhiều điều kiện, tra cứu ngang, tra cứu ở file khác, và sử dụng ký tự đại diện, cách xử lý các lỗi thường gặp khi dùng hàm này. Hy vọng bài viết này đã giúp bạn hiểu rõ hơn về hàm Vlookup trong Excel và cách sử dụng nó hiệu quả.
Những kiến thức về hàm Excel