Hàm VLOOKUP là một hàm đặc biệt hữu ích trong Excel của hãng phần mềm Microsoft. Tuy nhiên, nhiều người sử dụng Excel lâu năm cũng chưa hẳn hiểu rõ hàm VLOOKUP là gì. Hãy cùng HASUKA tìm hiểu kỹ hơn qua bài viết này nhé!

1. Hàm VLOOKUP là gì?

Hàm VLOOKUP được sử dụng khi bạn cần dò tìm dữ liệu trong một bảng, một phạm vi theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang tương ứng.

Trong thực tế, hàm VLOOKUP cực kỳ thông dụng khi tìm tên sản phẩm, đơn giá, số lượng,.. dựa trên mã vạch, 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 tiêu chí trên.

Ngược lại, khi bạn cần dò tìm dữ liệu trong một bảng, một phạm vi theo hàng ngang và trả về dữ liệu tương ứng theo hàng dọc thì hãy sử dụng hàm HLOOKUP.

LOOKUP là Look Up nghĩa là tìm kiếm trong tiếng Anh. V là viết viết tắt của Vertical - hàng dọc và H là viết tắt của Horizontal - hàng ngang.

VLOOKUP là một hàm hữu hiệu của Excel

2. Công thức hàm VLOOKUP

Nếu bạn copy công thức cho các ô dữ liệu khác, bạn cần sử dụng dấu $ để cố định Table_array đề giới hạn dò tìm bằng cách thêm trực tiếp trước khai báo (VD: $H$6:$J$13), cột hoặc sử dụng nút F4 sau khi chọn bảng.

Hàm VLOOKUP có công thức khá dài

Xem thêm: Hàm Sumif trong excel 

3. Ví dụ hàm VLOOKUP

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

Do tình hình Covid-19, công ty quyết định phụ cấp cho nhân viên theo chức vụ tương ứng như bảng 2 (B16:C21). Lúc này, dựa vào danh sách nhân viên cùng với chức vụ có sẵn ở bảng 1, ta sẽ xác định mức phụ cấp tương ứng.

Bạn sử dụng hàm VLOOKUP để tính phụ cấp theo chức vụ cho nhân viên

Cách thực hiện là bạn sẽ dò tìm giá trị của chức vụ của nhân viên tại bảng 1, sau đó dò tìm tại cột 1 trong bảng 2 từ trên xuống dưới. Khi bạn tìm thấy giá trị, bạn sẽ lấy giá trị tương ứng tại cột 2 của bảng 2 để điền vào bảng 1.

Với một danh sách nhân viên vài trăm, một ngàn người thì bạn không thể làm thủ công như vậy. Đó chính là cách hàm VLOOKUP phát huy tác dụng.

Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$B$16:$C$21,2,0)

• Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác.
• 2 là số thứ tự của cột dữ liệu.
• Range_lookup = 0 (FALSE) để dò tìm chính xác.

Bạn nhập công thức VLOOKUP(D4,$B$16:$C$21,2,0) để thu dữ liệu

Sau đó, bạn chỉ cần copy công thức cho các ô khác hoặc sử dụng Flash Fill và bạn đã hoàn thành việc tính phụ cấp theo chức vụ nhanh chóng.

Bạn tiến hành copy công thức cho các dòng khác để tiết kiệm tính phụ cấp cho nhân viên

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

Sau kỳ kiểm tra, ta có kết quả bài thi tương ứng với học sinh như bảng 1. Ta cần xếp loại theo điểm số dựa trên bảng 2 (B11:C15).

Bạn sử dụng hàm VLOOKUP để xếp loại học sinh theo điểm số

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

• Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác.
• 2 là số thứ tự của cột dữ liệu.
• Range_lookup = 1 (TRUE) để dò tìm điểm số có giá trị gần nhất.

Excel sẽ lấy điểm số ở cột D4 và dò trong bảng 2. Khi thấy giá trị gần nhất của D4 trong bảng (ở đây là 8.5), Excel sẽ trả về kết quả tương ứng ở cột 2 là Giỏi.

Bạn điền công thức =VLOOKUP(D4,$B$11:$C$15,2,1) để tiến hành

Sau đó, bạn chỉ cần copy công thức cho các ô khác hoặc sử dụng Flash Fill và bạn đã hoàn thành việc xếp loại học sinh theo điểm số nhanh chóng.

Bạn có thể sử dụng Flash Fill để hoàn thành việc xếp loại học sinh theo điểm số nhanh chóng hơn

Ví dụ 3: Dùng hàm VLOOKUP để tìm kiếm gần đúng

Ví dụ: Để xếp loại học sinh dựa trên điểm trung bình trong trường hợp sau, Range_lookup=1, ta bắt buộc phải lấy giá trị tương đối nghĩa là gần đúng, 9.1 gần với 9, 5.3 gần với 5… hay diễn đạt theo một cách khác, chúng ta có thể đưa ra tiêu chí xếp loại như sau:

• Từ 9: xếp loại giỏi ( lớn hơn hoặc bằng 9, 9 <= x)
• Từ 6.5 đến dưới 9: xếp loại khá (6.5 <= x < 9)
• Từ 5 đến dưới 6.5: xếp loại trung bình (5 <= x < 6.5)

Tại ô D4, bạn điền công thức: = VLOOKUP(C4,$B$10:$C$12,2,TRUE)

Bạn có thể sử dụng hàm VLOOKUP để tìm kiếm gần đúng

Ví dụ 4: Hàm VLOOKUP kết hợp với Data Validation

Đầu tiên, bạn chọn ô C10, sau đó bấm vào thẻ Data / Data Validation để chuẩn bị Data Validation.

Đầu tiên bạn vào thẻ Data Validation

Tiếp theo, khi hộp thoại Data Validation mở ra, bạn bấm chọn thẻ Settings. Ở mục Allow, bạn tiến hành chọn List và cuối cùng chọn Source là vùng B4:B7.

Bạn vào Setting và làm theo hướng dẫn

Cuối cùng, bạn điền công thức sau vào bảng Excel: =VLOOKUP(C10,B4:D7,2,0) và =VLOOKUP(C10,B4:D7,3,0) để tham chiếu đến ô chứa Data Validation.

Bạn nhập công thức để Excel tiến hành tham chiếu

Ví dụ 5: Tìm mã ngành học

Dựa vào bảng mã ngành, bạn hãy sử dụng hàm VLOOKUP để có thể tìm ra tên ngành học của sinh viên dựa trên ký tự đầu tiên của số báo danh.

Bạn sử dụng kết hợp hàm VLOOKUP với hàm LEFT để điền vào ô cần tìm mã ngành như công thức sau: =VLOOKUP(LEFT(C10),F4:G5,2,0)

Trong đó, hàm LEFT(C10) sẽ giúp bạn lấy ra ký tự đầu tiên trong số báo danh được lưu trong ô C10, và hàm VLOOKUP sẽ sử dụng ký tự này cho việc tra cứu trong bảng mã ngành và cho ra kết quả.

Bạn có thể sử dụng kết hợp hàm VLOOKUP với hàm LEFT để tìm mã ngành học

4. Các lỗi thường gặp khi dùng hàm VLOOKUP

Lỗi #N/A

Một ràng buộc của hàm VLOOKUP là nó chỉ có thể tìm các giá trị trên cột ngoài cùng bên trái trong Table_array, nếu không sẽ xuất hiện lỗi #N/A. Lúc này bạn hãy cân nhắc sử dụng hàm INDEX kết hợp hàm MATCH.

Như ví dụ bên dưới, Table_array là A2:C10, nên hàm VLOOKUP sẽ tìm kiếm trong cột A. Để sửa trường hợp này, bạn đổi Table_array thành B2:C10, hàm VLOOKUP sẽ tìm kiếm trong cột B.

Ngoài ra, nếu không tìm thấy kết quả khớp chính xác sẽ trả về hàm sẽ trả về lỗi #N/A do dữ liệu không có trong Table_array. Lúc này bạn có thể sử dụng hàm IFERROR để đổi #N/A thành giá trị khác.

Như ví dụ bên dưới, "Rau muống" không có trong bảng dò tìm nên hàm VLOOKUP sẽ không tìm thấy.

Nếu bạn chắc chắn rằng dữ liệu có trong Table_array của mình và hàm VLOOKUP không tìm được được, hãy kiểm tra lại rằng các ô dữ liệu được tham chiếu không có khoảng trắng ẩn hoặc ký tự không in. Ngoài ra, hãy đảm bảo rằng các ô dữ liệu tuân theo đúng định dạng.

Lỗi #REF!

Nếu Col_index_num lớn hơn số cột trong Table_array, bạn sẽ nhận được giá trị lỗi #REF!. Lúc này, bạn hãy kiểm tra lại công thức để đảm bảo Col_index_num bằng hoặc nhỏ hơn số cột trong Table_array.

Như ví dụ bên dưới, Col_index_num là 3, trong khi Table_array là B2:C10 chỉ có 2 cột.

Lỗi #VALUE!

Nếu Col_index_num nhỏ hơn 1 trong công thức, bạn sẽ nhận giá trị lỗi #VALUE!.

Trong Table_array, cột 1 là cột tìm kiếm, cột 2 là cột đầu tiên ở bên phải của cột tìm kiếm, v.v... Vì vậy khi xuất hiện lỗi này, hãy kiểm tra lại giá trị Col_index_number trong công thức.

Như ví dụ bên dưới, Col_index_num bằng 0 dẫn đến việc xuất hiện lỗi #VALUE!.

Lỗi #NAME?

Lỗi #NAME? xuất hiện khi Lookup_value thiếu dấu ngoặc kép ("). Để tìm kiếm giá trị định dạng văn bản (Text), bạn dùng dấu ngoặc kép để Excel có thể hiểu công thức.

Như ví dụ bên dưới, Cải xoăn không bỏ vào dấu ngoặc kép (") sẽ làm xuất hiện lỗi #NAME?. Bạn sửa lỗi bằng cách thay Cải xoăn thành "Cải xoăn".

5. Một số lưu ý khi dùng hàm VLOOKUP

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

Trong quá trình copy công thức, hãy biến Table_array hoặc Lookup_value thành tham chiếu tuyệt đối bằng cách đặt dấu đô la ($) trước các cột và hàng để công thức không bị thay đổi.

Như ví dụ dưới, ta có công thức tại ô C13 là =VLOOKUP(B13,$B$2:$C$10,2,0). Khi copy công thức cho ô C4 Table_array sẽ giữ nguyên.

Nếu không chuyển đổi thành tham chiếu tuyệt đối, Lookup_value hoặc Table_array sẽ bị thay đổi, làm kết quả tìm kiếm bị sai lệch.

Như ví dụ dưới, ta có công thức tại ô C13 là =VLOOKUP(B13,B2:C10,2,0). Khi copy công thức cho ô C4 Table_array sẽ biến thành =VLOOKUP(B14,B3:C11,2,0).

Không lưu trữ giá trị số dưới dạng văn bản

Nếu trong Table_array, dữ liệu số đang để dưới dạng văn bản và Lookup_value lại là dạng số thì hàm VLOOKUP sẽ trả về lỗi #N/A.

Như ví dụ dưới, ta có dữ liệu tại ô A2:A5 đang ở dạng văn bản nhưng Lookup_value tại ô A8 đang ở dạng số.

Trong trường hợp này, hãy chuyển định dạng ô A2:A5 thành dạng số và hàm sẽ trả về kết quả bình thường.

Bảng dò tìm chứa những giá trị bị trùng

Nếu bảng biểu của bạn chứa nhiều giá trị trùng nhau, hàm VLOOKUP sẽ trả về kết quả đầu tiên mà nó tìm thấy từ trên xuống dưới.

Như ở ví dụ dưới, trong bảng ta có 2 giá trị ứng với Táo là 97 và 23. Hàm VLOOKUP sẽ trả về kết quả 97 vì đó là giá trị đầu tiên nó tìm thấy

Giải pháp 1: Nếu bạn muốn loại bỏ giá trị trùng lặp, bạn bôi đen bảng dò tìm và chọn Data > Remove Duplicates

Giải pháp 2: Sử dụng Pivot Table để lọc ra danh sách kết quả

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

Chỉ hỗ trợ tham chiếu từ trái sang phải

Hàm VLOOKUP chỉ tìm kiếm giá trị từ trái sang phải trong dãy dữ liệu. Nếu bạn cần thực hiện tham chiếu từ phải sang trái, bạn cần phải kết hợp nhiều công thức lại với nhau như công thức Match và Index rất phức tạp và rắc rối, gây khó hiểu cho người sử dụng.

Bạn có thể kết hợp hàm Match và Index để dò tìm từ phải sang trái

Chỉ hoạt động hiệu quả với các giá trị riêng biệt

Hàm VLOOKUP chỉ hoạt động hiệu quả với các giá trị riêng biệt vì có xu hướng chỉ quan tâm đến giá trị tham chiếu đầu và bỏ quên các dòng khác chứa giá trị tương tự.

Chỉ hoạt động hiệu quả với các giá trị riêng biệt là một trong những hạn chế của hàm VLOOKUP

Số thứ tự cột tham chiếu luôn cố định

Hàm VLOOKUP chỉ cho phép bạn điền thủ công các số thứ tự của cột tham chiếu vào nội dung của công thức, làm cho các thao tác sao chép sang ô tính khác gặp nhiều khó khăn hơn. Lúc này, bạn cần sử dụng hàm Match để biến biến đổi số thứ tự liên tục.

Bạn có thể sử dụng hàm Match để khắc phục hạn chế số thứ tự cột tham chiếu luôn cố định của hàm VLOOKUP

Mặc định thiết lập “approximate match”

Điều kiện kiểm duyệt tham chiếu ở trong nội dung hàm VLOOKUP nếu không được chọn thì công thức sẽ mặc định thiết lập “approximate match”. Điều này điều này có thể gây ra kết quả không chính xác, sai lệch khi tham chiếu.

Điều kiện kiểm duyệt tham chiếu ở trong nội dung hàm Vlookup là approximate match

Làm tốc độ phản hồi của bảng tính bị chậm đi

Hàm VLOOKUP cũng có có thể làm giảm tốc độ phản hồi của bảng tính giảm hiệu năng của chương trình, thậm chí gây ra hiện tượng crash, gây khó chịu cho người sử dụng.

Hàm VLOOKUP có thể làm tốc độ phản ứng của bảng tính chậm đi