Hàm VLOOKUP trong Excel là gì? Cấu trúc và ví dụ chi tiết

Hàm VLOOKUP là một trong những hàm phổ biến nhất trong Excel, có chức năng tìm kiếm theo chiều dọc và trả về giá trị tương ứng trong bảng dữ liệu. Hàm này đã trở thành công cụ không thể thiếu đối với nhiều người dùng, đặc biệt là dân văn phòng, kế toán, nhân sự và những ai thường xuyên làm việc với bảng tính. Bài viết dưới đây sẽ giúp bạn nắm rõ hơn về VLOOKUP, cách sử dụng cú pháp và các ví dụ chi tiết để bạn dễ dàng áp dụng trong công việc.

Hàm VLOOKUP trong Excel là gì? Lợi ích khi sử dụng?

Hàm VLOOKUP (viết tắt của “Vertical Lookup” – tra cứu theo cột dọc) là một công cụ để tìm kiếm một giá trị trong cột đầu tiên của bảng dữ liệu, sau đó trả về giá trị tương ứng từ một cột khác cùng hàng trong bảng.

Thực tế, VLOOKUP là một trong những hàm tra cứu được sử dụng nhiều nhất trong Excel. Hàm này rất hữu ích để tra cứu các thông tin như tên sản phẩm, giá trị, mã số, điểm số, v.v. Ví dụ, nếu bạn có một danh sách mặt hàng với mã và giá, bạn có thể sử dụng VLOOKUP để nhanh chóng tìm được giá của bất kỳ mặt hàng nào chỉ dựa trên mã của nó.

Việc áp dụng hàm VLOOKUP mang lại rất nhiều lợi ích khi làm việc với Excel. Cụ thể:

• Hàm này giúp bạn tiết kiệm thời gian khi tra cứu dữ liệu trong các bảng tính lớn mà không cần phải tìm kiếm thủ công. 
• VLOOKUP đảm bảo độ chính xác cao, giúp giảm thiểu sai sót khi xử lý thông tin lặp đi lặp lại. 
• VLOOKUP có khả năng tự động hóa công việc, đặc biệt khi kết hợp với các hàm khác như IF, ISERROR hay IFERROR. 

Nhờ những tính năng này, người dùng có thể tạo ra các bảng tính thông minh, linh hoạt và dễ dàng quản lý hơn trong công việc hàng ngày.

So sánh hàm VLOOKUP và hàm HLOOKUP

Hàm VLOOKUP và hàm HLOOKUP có những sự khác biệt cơ bản như sau:

Cấu trúc chuẩn của hàm VLOOKUP trong Excel

Cách viết chính xác của hàm VLOOKUP trong Excel được mô tả như sau:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Chi tiết mô tả:

Hướng dẫn sử dụng VLOOKUP với ví dụ minh họa chi tiết

Hàm VLOOKUP trong Excel có nhiều cách sử dụng linh hoạt. Tùy vào từng mục đích công việc, bạn có thể áp dụng hàm này để giải quyết các vấn đề khác nhau. Dưới đây là một số ví dụ cụ thể để bạn tham khảo.

Sử dụng VLOOKUP giữa các sheet

Hàm VLOOKUP không chỉ giúp bạn tra cứu dữ liệu trong cùng một sheet mà còn có thể linh hoạt làm việc giữa hai hay nhiều sheet khác nhau. Dưới đây là một ví dụ cụ thể để bạn hiểu rõ cách sử dụng hàm này.

Ví dụ: Sử dụng hàm VLOOKUP trong Excel để tính toán lương cho các nhân viên trong hai sheet dưới đây:

Các bước thực hiện:

Bước 1: Tại sheet 1, bạn nhập công thức như sau: =VLOOKUP(Sheet1!D3).

Bước 2: Tiếp theo, chuyển sang sheet 2 và nhập công thức tương tự như ví dụ dưới đây.

Bước 3: Tại bước này, bạn cần sao chép công thức VLOOKUP từ Sheet 2 vào ô E3 và nhấn Enter. Ngay lập tức, ô chứa “Lương” sẽ hiển thị kết quả chính xác là 5000, giống như bảng dữ liệu trong sheet 2.

Bước 4: Bạn hãy sao chép công thức từ ô E3 vào các ô tiếp theo (ví dụ từ E4 đến E8) bằng cách nhấp vào ô E3. Sau đó, kéo hình vuông nhỏ ở góc dưới cùng bên phải ô E3 xuống các ô cần áp dụng công thức.

Sử dụng hàm VLOOKUP để tra cứu giá trị gần đúng

Giả sử bạn đang sử dụng hàm VLOOKUP trong trường hợp này để quản lý danh sách nhân viên trong Excel. Danh sách này bao gồm các cột như mã nhân viên, tên và chức vụ. Ngoài ra, bạn còn có một bảng phụ chứa thông tin về mã nhân viên, quê quán và trình độ học vấn. Mục tiêu là tự động điền thông tin quê quán và trình độ học vấn vào bảng chính dựa trên mã nhân viên.

Ví dụ: Bạn có danh sách thông tin của các nhân viên như sau:

Để giải quyết tình huống này, đầu tiên tại ô D4 (cột Quê quán trong bảng chính), bạn nhập công thức: =VLOOKUP($A4,$A$16:$C$25,2,0). Sau đó, nhấn Enter để hoàn thành.

Tiếp theo, bạn kéo công thức xuống để áp dụng cho tất cả các nhân viên còn lại trong bảng.

Tại ô E4 của cột trình độ học vấn, bạn nhập công thức sau: =VLOOKUP($A4,$A$16:$C$25,3,0).

Nhấn Enter sau đó kéo công thức để kết quả hiển thị như mong muốn.

Sử dụng VLOOKUP để phân loại học sinh

Trong ví dụ dưới đây, chúng ta sẽ dùng hàm VLOOKUP để phân loại học lực cho các học sinh dựa vào điểm trung bình. Bảng xếp loại được sắp xếp từ yếu đến giỏi. Vì vậy, công thức VLOOKUP sẽ giúp ta tìm kiếm giá trị tương đối trong bảng. Dưới đây là hai bảng mẫu mà bạn có thể tham khảo.

Theo bảng Excel trên, giá trị cần tìm nằm ở cột C, bắt đầu từ dòng thứ 6. Phạm vi tìm kiếm là $A$18:$B$21, và cột chứa giá trị cần tìm là cột số 2.

Tại ô D6, bạn cần nhập công thức =VLOOKUP($C6,$A$18:$B$21,2,1) để dò tìm các giá trị tương đối. Nếu muốn, bạn có thể thay thế bằng công thức =VLOOKUP($C6,$A$18:$B$21,2,0) để tìm giá trị chính xác hơn. Sau đó, nhấn Enter để hoàn thành.

Bây giờ, bạn chỉ cần sao chép công thức này vào các ô cần hiển thị kết quả.

Cuối cùng, kết quả của ví dụ này sẽ được thể hiện như sau:

Sử dụng hàm VLOOKUP để tính toán mức thưởng cho nhân viên theo cấp bậc.

Tình huống: 

Trong bối cảnh dịch Covid-19, công ty quyết định cung cấp phụ cấp cho nhân viên tùy thuộc vào chức vụ của họ, dựa trên bảng phụ cấp (Bảng 2, phạm vi ô B16:C21). Nhiệm vụ của bạn là sử dụng hàm VLOOKUP để tra cứu mức phụ cấp tương ứng cho từng nhân viên đã có tên và chức vụ trong Bảng 1.

Cách thực hiện:

Tại ô E4 trong Bảng 1 (cột phụ cấp), nhập công thức sau: =VLOOKUP(D4, $B$16:$C$21, 2, FALSE). Trong đó:

• D4: Chứa chức vụ của nhân viên.
• $B$16:$C$21: Phạm vi bảng tra cứu gồm chức vụ và phụ cấp, với dấu $ cố định vùng dữ liệu khi sao chép công thức.
• 2: Số thứ tự cột trả về kết quả (cột phụ cấp).
• FALSE: Yêu cầu tìm kiếm chính xác chức vụ.

Sau khi hoàn thành công thức, bạn có thể sao chép xuống các dòng còn lại hoặc sử dụng tính năng Flash Fill của Excel. Như vậy, bạn đã tính được phụ cấp cho các nhân viên trên 2 sheet một cách nhanh chóng và chính xác.

Một số lỗi phổ biến khi sử dụng hàm VLOOKUP

Khi làm việc với hàm VLOOKUP, người dùng thường gặp phải một số lỗi dưới đây.

Lỗi #NAME?

Lỗi #NAME? thường xảy ra khi giá trị tìm kiếm (Lookup_value) là một chuỗi văn bản nhưng lại không được bao quanh trong dấu ngoặc kép (“”). Khi thao tác với chuỗi ký tự trong Excel, hãy đảm bảo rằng nội dung cần tìm được đặt trong dấu ngoặc kép để Excel có thể nhận diện đúng.

Ví dụ: Nếu bạn nhập =VLOOKUP(Cải xoăn, A2:B10, 2, 0), Excel sẽ thông báo lỗi #NAME?. Điều này xảy ra vì ứng dụng không nhận diện được “Cải xoăn” như một chuỗi văn bản hợp lệ.

Cách khắc phục: Để sửa lỗi, bạn chỉ cần thêm dấu ngoặc kép quanh chuỗi văn bản, ví dụ như =VLOOKUP(“Cải xoăn”, A2:B10, 2, 0).

Lỗi #N/A

Lỗi #N/A thường xảy ra khi Excel không thể tìm thấy giá trị cần tra cứu trong phạm vi dữ liệu đã chỉ định. Dưới đây là một số nguyên nhân chính gây ra lỗi này:

Giá trị cần tìm không nằm ở cột đầu tiên của bảng dò (Table_array)

Hàm VLOOKUP chỉ có khả năng tìm kiếm giá trị trong cột đầu tiên của vùng dữ liệu bạn chỉ định. Nếu giá trị bạn cần tìm nằm ngoài cột này, Excel sẽ không thể tìm thấy và sẽ trả về lỗi #N/A. Ví dụ, nếu bảng dò là A2:C10, VLOOKUP sẽ chỉ tra cứu trong cột A. Để tìm kiếm trong cột B, bạn cần thay đổi phạm vi bảng dò thành B2:C10.

Không tìm thấy giá trị trùng khớp

Nếu giá trị bạn đang tìm kiếm không có trong bảng dò, Excel sẽ trả về lỗi #N/A. Trong trường hợp này, bạn có thể sử dụng hàm IFERROR để thay thế lỗi này bằng thông báo dễ hiểu hơn, như: =IFERROR(VLOOKUP(…), “Không tìm thấy”). Ví dụ, khi bạn tìm kiếm “Rau muống” mà không có dữ liệu trong bảng, kết quả sẽ là #N/A.

Dữ liệu bị ẩn khoảng trắng hoặc sai định dạng

Đôi khi dữ liệu có vẻ giống nhau nhưng lại khác biệt do có sự tồn tại của khoảng trắng thừa, ký tự đặc biệt, hoặc sai định dạng (chữ và số bị đảo lộn). Nếu bạn chắc chắn rằng giá trị tìm kiếm có trong bảng nhưng vẫn gặp lỗi #N/A, hãy kiểm tra kỹ các ô dữ liệu để đảm bảo chúng đúng định dạng và không có ký tự ẩn hoặc khoảng trắng thừa.

Lỗi #VALUE!

Lỗi #REF! xuất hiện khi chỉ số cột trả về (Col_index_num) lớn hơn số lượng cột có trong phạm vi bảng dò (Table_array) bạn đã chọn. Cụ thể, tham số Col_index_num trong công thức VLOOKUP quy định cột sẽ được sử dụng để trả về kết quả. Nếu số cột bạn chỉ định vượt quá số lượng cột có sẵn trong bảng dò, Excel sẽ không thể lấy dữ liệu và gây ra lỗi #REF!.

Ví dụ: Bạn viết công thức như sau: =VLOOKUP(A2, B2:C10, 3, FALSE). Tuy nhiên, vùng dữ liệu B2:C10 chỉ có 2 cột (B và C), trong khi bạn lại yêu cầu lấy dữ liệu từ cột thứ 3, do đó Excel sẽ báo lỗi #REF! vì không thể tìm thấy cột thứ ba trong phạm vi đã chọn.

Cách khắc phục: Bạn cần kiểm tra lại số lượng cột trong Table_array để đảm bảo rằng Col_index_num không vượt quá số lượng cột trong bảng dò.

H3 Lỗi #REF!

Lỗi #VALUE! xảy ra khi tham số Col_index_num trong công thức VLOOKUP nhỏ hơn 1. Col_index_num chỉ số cột trả về, và số nhỏ nhất có thể là 1. Nếu bạn nhập giá trị dưới 1, Excel sẽ không hiểu và sẽ trả về lỗi #VALUE!.

Ví dụ: Công thức: =VLOOKUP(A2, B2:D10, 0, FALSE). Trong trường hợp này, Col_index_num = 0 → Excel sẽ thông báo lỗi #VALUE! vì không có cột nào mang số thứ tự 0 để trả về kết quả.

Cách khắc phục: Bạn cần kiểm tra lại và đảm bảo rằng Col_index_num luôn là một số nguyên từ 1 trở lên. Hãy xác định đúng số thứ tự của cột trong bảng để tránh nhập sai.

Cách kết hợp hàm VLOOKUP với các hàm khác

Dưới đây là cách sử dụng hàm VLOOKUP kết hợp với các hàm khác để mở rộng khả năng tra cứu dữ liệu và giải quyết nhiều tình huống phức tạp trong Excel:

Kết hợp VLOOKUP với hàm LEFT / RIGHT

Hàm LEFT và RIGHT cho phép bạn trích xuất một phần ký tự từ chuỗi văn bản, với LEFT lấy từ đầu chuỗi, còn RIGHT lấy từ cuối chuỗi.

Cú pháp của hàm số: =LEFT(text, num_chars) / =RIGHT(text, num_chars)

Trong đó:

• text: Chuỗi văn bản hoặc địa chỉ ô chứa chuỗi cần trích xuất.
• num_chars: Số ký tự bạn muốn lấy, từ trái với LEFT và từ phải với RIGHT.

Ví dụ: Bạn có một danh sách sinh viên, mỗi mã sinh viên gồm mã ngành học ở 4 ký tự đầu. Nhiệm vụ của bạn là tạo công thức để tra cứu ngành học dựa trên mã ngành.

Gợi ý: Tại ô C2, bạn có thể nhập công thức =VLOOKUP(LEFT(A2;4);$A$8:$B$10;2;0). Kết quả sẽ tự động được hiển thị ngay dưới dạng danh sách ngành học tương ứng.

Kết hợp VLOOKUP và hàm CHOOSE

Khi cần dò tìm dữ liệu dựa trên nhiều tiêu chí hoặc thay đổi vị trí các cột trong bảng, bạn có thể kết hợp hàm VLOOKUP với hàm CHOOSE.

Cú pháp chuẩn: =VLOOKUP(Lookup_value;CHOOSE({1;2};vùng giá trị 1;vùng giá trị 2); Col_index_num; Range_lookup)

Ví dụ: Bạn muốn tra cứu mức giá của sản phẩm dựa trên tên sản phẩm và hãng sản xuất, với thông tin cả hai đã có sẵn.

Tại ô B14, bạn nhập công thức =VLOOKUP(B12&B13, CHOOSE({1,2}, $A$2:$A$10&$B$2:$B$10, $C$2:$C$10), 2, 0).

Kết hợp hàm VLOOKUP và hàm IF

Hàm IF có thể được kết hợp với VLOOKUP để tạo ra các công thức linh hoạt tùy thuộc vào mục đích sử dụng. Kết quả sẽ thay đổi tùy vào vị trí mà bạn lồng ghép các hàm này với nhau.

Ví dụ: Giả sử bạn có bảng học phí của sinh viên như hình dưới đây, bạn cần điền học phí cho các sinh viên dựa vào ngành học của họ. Lưu ý rằng mỗi ngành học có mức học phí khác nhau, tùy thuộc vào cách đóng học phí của sinh viên.

Do đó, kết quả chính xác tại ô E2 sẽ được hiển thị như sau:

Hàm VLOOKUP kết hợp với hàm SUM

Hàm VLOOKUP có thể được kết hợp với hàm SUM để xử lý các tình huống yêu cầu tính tổng các giá trị trước khi thực hiện tra cứu. Nếu bạn muốn tìm kiếm một giá trị sau khi đã tính tổng từ nhiều ô, bạn chỉ cần sử dụng hàm SUM trong phần giá trị dò tìm (lookup_value) của công thức VLOOKUP.

Cú pháp chuẩn: =VLOOKUP(SUM(a;b;…);Table_array; Col_index_ num; Range_lookup)

Ví dụ: Giả sử có thông tin như trong hình dưới đây, bạn cần tính toán số tiền thưởng cho các nhân viên trong bảng. Lưu ý, số hàng mà mỗi nhân viên đã hoàn thành sẽ được tính bằng tổng số hàng của quý 1 và quý 2.

Kết quả chính xác cho ô D2 sẽ là =VLOOKUP(SUM(B2;C2);$A$9:$B$11;2;0).

Hàm VLOOKUP kết hợp với hàm MID

Hàm MID hoạt động tương tự như các hàm LEFT và RIGHT, nhưng điểm đặc biệt là bạn có thể lấy ký tự bắt đầu từ bất kỳ vị trí nào trong chuỗi văn bản.

Cú pháp: =MID(a;b;c)

Trong đó:

• a: Chuỗi văn bản hoặc địa chỉ ô mà bạn muốn lấy ký tự.
• b: Vị trí bắt đầu lấy ký tự (đếm từ trái sang phải).
• c: Số lượng ký tự bạn muốn lấy.

Do đó, khi kết hợp hàm MID với VLOOKUP, công thức chuẩn sẽ như sau: =VLOOKUP(MID(a;b;c); Table_array; Col_index_num; Range_lookup).

Ví dụ: Giả sử bạn có đề bài như hình dưới, nhiệm vụ của bạn là điền ngành học của các sinh viên vào bảng. Lưu ý, thông tin ngành học của các bạn sinh viên được xác định hoàn toàn từ mã ngành trong bảng dưới đây.

Vì vậy, đáp án chính xác cho ô C2 sẽ là =VLOOKUP(MID(A2;4;4); $A$8:$B$10; 2; 0).

Những lưu ý khi sử dụng hàm VLOOKUP để tránh các lỗi phổ biến.

Khi sử dụng hàm VLOOKUP trong Excel, bạn cần lưu ý một số nguyên tắc quan trọng để tránh gặp phải lỗi và tối ưu hóa hiệu quả công việc:

• Vị trí cột tra cứu: Cột chứa giá trị cần tra cứu phải là cột đầu tiên trong vùng dữ liệu (Table_array). Nếu không, công thức sẽ không hoạt động chính xác.
• Kiểu đối sánh: Chọn TRUE (hoặc bỏ qua) nếu bạn cần đối sánh tương đối, ngược lại, chọn FALSE để có đối sánh chính xác – trường hợp này thường gặp hơn.
• Độ chính xác khi tìm kiếm: Nếu không xác định rõ range_lookup, Excel sẽ mặc định TRUE, điều này có thể gây ra kết quả không chính xác. Vì thế, bạn nên chọn FALSE khi cần kết quả chính xác tuyệt đối.
• Xử lý dữ liệu trùng lặp: Khi cột tra cứu có giá trị trùng lặp, VLOOKUP chỉ trả về giá trị đầu tiên nó tìm thấy.
• Giới hạn về hướng tra cứu: VLOOKUP chỉ có thể tra cứu dữ liệu ở bên phải cột tra cứu. Bạn không thể tìm kiếm ngược sang bên trái.

Tóm lại

Hàm VLOOKUP là một công cụ vô cùng mạnh mẽ và hữu ích trong Excel, đặc biệt khi xử lý các bảng dữ liệu lớn, giúp bạn tra cứu thông tin nhanh chóng và chính xác. Vì vậy, hãy thường xuyên luyện tập và kết hợp VLOOKUP với các hàm khác như IF, SUM, LEFT, MID… để tận dụng tối đa khả năng của hàm này trong Excel nhé!