Nhã Linh
Nhã Linh PRO
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 6831 lượt xem

23 điều bạn nhất định phải biết khi sử dụng hàm VLOOKUP (Phần 1)

Oct 16 2021

Không có gì đáng bàn cãi khi nói hàm VLOOKUP là hàm tìm kiếm phổ biến bậc nhất trong Excel. Có thể bạn đã quá quen với công thức hàm VLOOKUP, nhưng liệu bạn đã nắm được tất cả những gì cần biết về hàm này? Hãy cùng Gitiho kiểm tra kiến thức về hàm VLOOKUP trong Excel ngay thôi nào!

Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

Có lẽ điểm tuyệt vời nhất của hàm VLOOKUP chính là cú pháp dễ sử dụng, do đó, người dùng có thể dễ dàng tham chiếu đúng dữ liệu mà không gặp phải khó khăn gì. Tuy nhiên, trên thực tế, có rất nhiều lỗi bạn có thể gặp phải với cách dùng hàm VLOOKUP trên trang tính của mình.

Thay vì chờ đến lúc đối mặt với lỗi #N/A trong hàm VLOOKUP, hay phải giải trình với sếp vì lỗi số liệu trong báo cáo đến từ kết quả sai lệch của hàm VLOOKUP, mình chắc chắn rằng bạn sẽ cần ghi nhớ 23 điều dưới đây để nắm được cách dùng hàm VLOOKUP trong Excel chuẩn xác nhất.

Những điều cần lưu ý về hàm VLOOKUP

Cách hàm VLOOKUP hoạt động

Hàm VLOOKUP là một hàm tìm kiếm trong Excel có chức năng tìm kiếm và trả về giá trị hiện có trên trang tính Excel. Chữ "V" trong tên hàm là viết tắt của từ Tiếng Anh "Vertical" (theo chiều dọc), nghĩa là cấu trúc trang tính cần được sắp xếp theo chiều dọc với dữ liệu nằm tại các hàng ngang. Nếu bạn cần một hàm tìm chiếu trong Excel dành cho cấu trúc trang tính chiều ngang, hãy tham khảo hàm HLOOKUP.

Nói một cách đơn giản, nếu bạn có một bảng Excel nằm dọc với tiêu đề dữ liệu ở đầu mỗi cột, và có các cột cần tra cứu ở bên trái, nghĩa là bạn có thể sử dụng hàm VLOOKUP.

Cú pháp hàm VLOOKUP trong Excel như sau:

=VLOOKUP(lookup_value, table_array, col_index_ num, [range_lookup])

Trong đó:

  • lookup_value là giá trị cần dò tìm.
  • table_array là bảng giới hạn để dò tìm.
  • col_index_num là số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
  • range_lookup là giá trị Logic (TRUE=1, FALSE=0) xác định Excel cần so sánh, tìm kiếm chính xác hay so sánh, tìm kiếm tương đối với bảng giới hạn
    • Nếu Range_lookup = 1 (TRUE): Tìm kiếm tương đối.
    • Nếu Range_lookup = 0 (FALSE): Tìm kiếm chính xác.
    • Nếu bỏ qua đối này thì Excel hiểu là range_lookup = 1 một cách mặc định.

Hàm VLOOKUP có yêu cầu đặc biệt đối với cấu trúc dữ liệu của bạn, đó là giá trị cần tra cứ cần phải xuất hiện ở cột bên trái so với giá trị cần trả về. Mình sẽ lấy ví dụ về cách dùng hàm VLOOKUP trong hình dưới đây. 

Để tìm được địa chỉ email của đúng nhân viên, bạn sẽ cần sắp xếp bảng dữ liệu theo đúng các cột theo thứ tự từ 1 đến 6 tính từ trái sang. Vì cột Email là cột số 6 trong bảng Excel nên khi sử dụng hàm VLOOKUP, chúng ta sẽ điền số 6 vào tham số column_index. Ngoài ra, vì yêu cầu tìm kiếm chính xác email của nhân viên trong ô H3 nên chúng ta lựa chọn FALSE cho tham số range_lookup.

Xem thêm: Hướng dẫn cách dùng hàm VLOOKUP trong Excel qua các ví dụ từ cơ bản đến nâng cao

Hàm VLOOKUP chỉ tìm kiếm theo bên phải

Hàm VLOOKUP có một điểm yếu chí mạng, đó là chỉ tìm kiếm bên phải. Cụ thể hơn, hàm VLOOKUP trong Excel chỉ có thể trả về kết quả khi cột chứa kết quả nằm ở bên phải cột đầu tiên trong bảng. Thông thường, chúng ta sẽ sử dụng hàm VLOOKUP để tra cứu các giá trị ở cột đầu tiên, do đó không có bất cứ khó khăn gì với cách dùng hàm VLOOKUP vì toàn bộ giá trị cần trả về đều ở bên phải giá trị tra cứu.

Tuy nhiên, trong trường hợp giá trị tra cứu không nằm ở cột đầu tiên trong bảng dữ liệu Excel, hàm VLOOKUP sẽ ngay lập tức chào thua. Lúc này, bạn sẽ chỉ có thể lấy các giá trị nằm ở bên phải của cột giá trị tra cứu. Bên cạnh đó, bạn cũng cần chỉnh sửa tham số table_array sao cho cột giá trị tra cứu trở thành cột đầu tiên trong phạm vi cần tra cứu.

Như ở trong ví dụ bên trên, chúng ta đã sử dụng hàm LOOKUP để tìm email dựa vào tên nhân viên. Lý do chúng ta có thể sử dụng hàm VLOOKUP dễ dàng là bởi vì cột Nhân viên là cột đầu tiên trong bảng Excel, còn cột Email (cột 6) thì nằm ở phía bên phải so với cột Nhân viên.

Nếu như đổi lại thứ tự các cột và trình bày cột Email ở bên trái cột Nhân viên, ngay lập tức hàm VLOOKUP trong Excel sẽ xuất hiện lỗi #REF!

Với trang tính Excel này, khi tra cứu hàm VLOOKUP với giá trị nhân viên, bạn chỉ có thể tìm được ngày sinh, bộ phận làm việc, quê quán và ngày vào công ty của nhân viên. Ngoài ra, tham số table_array trong công thức hàm VLOOKUP cũng phải được đổi thành B2:F10 để cột Nhân viên trở thành cột đầu tiên trong phạm vi tra cứu.

Một trong các phương pháp khắc phục điểm yếu này của hàm VLOOKUP là hàm INDEX MATCH. Đây là một hàm tìm kiếm trong Excel cực kỳ mạnh mẽ và linh hoạt, có thể giúp bạn xử nhanh mọi thao tác tra cứu dữ liệu trên Excel. Hãy tham khảo bài viết dưới đây để nắm được cách sử dụng hàm INDEX MATCH nhé.

Xem thêm: Cách dùng hàm MATCH - trợ thủ đắc lực cho hàm tìm kiếm trong Excel

Hàm VLOOKUP chỉ trả về kết quả khớp đầu tiên

Nếu như hàm VLOOKUP tìm được nhiều hơn một giá trị khớp với giá trị tra cứu, hàm sẽ chỉ trả về giá trị khớp đầu tiên. Giả sử chúng ta có một bảng thông tin trên trang tính Excel như trong hình dưới đây.

Như bạn thấy, công thức hàm VLOOKUP trong Excel chỉ trả về một email của nhân viên Phạm Khánh Linh thuộc bộ phận kinh doanh mà hoàn toàn bỏ qua nhân viên cùng tên thuộc bộ phận marketing.

Kết quả này có thể thay đổi nếu cách dùng hàm VLOOKUP của chúng ta được thay đổi từ tìm kiếm tuyệt đối sang tìm kiếm tương đối.

Xem thêm: Hướng dẫn hàm VLOOKUP trong Excel cho người mới bắt đầu: Học với các ví dụ

Hàm VLOOKUP không phân biệt chữ hoa và chữ thường

Khi bạn sử dụng hàm VLOOKUP, bạn sẽ để ý rằng hàm VLOOKUP không phân biệt chữ hoa và chữ thường đối với giá trị tra cứu. Điều này nghĩa là một giá trị "ABC" trong tham số sẽ được hàm xử lý giống hệt giá trị "abc".

Hàm VLOOKUP có hai chế độ tìm kiếm

Như đề cập trong cú pháp hàm VLOOKUP, hàm có 2 chế độ tìm kiếm: Tìm kiếm tương đối và tìm kiếm tuyệt đối. Trong hầu hết các trường hợp, chúng ta sẽ sử dụng hàm VLOOKUP với chế độ tìm kiếm tuyệt đối vì chế độ này cho phép chúng ta tra cứu dữ liệu dựa trên một giá trị độc nhất, ví dụ như tra cứu thông tin sản phẩm dựa trên mã sản phẩm hay tra cứu thông tin bộ phim dựa trên tên phim.

Chế độ tìm kiếm tuyệt đối

Khi sử dụng chế độ tìm kiếm tuyệt đối, hàm VLOOKUP sẽ trả về kết quả khớp hoàn toàn với giá trị tra cứu. 

Dưới đây là một ví dụ với cách dùng hàm VLOOKUP trích xuất thông tin sản phẩm dựa trên mã hàng. Trong bảng Excel này, chúng ta cần các thông tin về tên sản phẩm, doanh thu của sản phẩm và thứ hạng cua sản phẩm trên bảng tổng hợp doanh thu của tháng. Do đó, chúng ta sẽ sử dụng chế độ tìm kiếm tuyệt đối để đảm bảo độ chính xác của kết quả hàm VLOOKUP.

Chế độ tìm kiếm tương đối

Hàm VLOOKUP có thể tìm kiếm tương đối nếu như dữ liệu của bạn không thể xác định giá trị chính xác cần trả về mà chỉ có thể tìm giá trị phù hợp nhất dựa theo giá trị tra cứu.

Giả sử trong ví dụ dưới đây, chúng ta cần tính thưởng dựa theo doanh thu bán hàng của các nhân viên bộ phận kinh doanh. Nếu như sử dụng hàm VLOOKUP với chế độ tìm kiếm tuyệt đối, chắc chắn chúng ta sẽ không thể xác định mức thưởng của từng nhân viên. Thay vào đó, chúng ta sẽ cần tìm giá trị mức thưởng phù hợp nhất với các giá trị doanh thu. Lúc này, cách dùng hàm VLOOKUP là tìm kiếm tương đối.

Trong công thức hàm VLOOKUP áp dụng cho cột % Thưởng (cột C), hãy nhớ sử dụng tham chiếu tuyệt đối cho phạm vi F2:G7 để tránh sai lệch kết quả khi kéo công thức xuống các ô trong cột nhé.

Xem thêm: Giới thiệu địa chỉ tuyệt đối trong Excel cố định ô trong công thức

Hàm VLOOKUP mặc định chế độ tìm kiếm tương đối

Chế độ tìm kiếm của hàm VLOOKUP được quyết định bởi tham số range_lookup. Tuy nhiên, vì đây là một tham số tùy chọn nên bạn có thể lựa chọn kết thúc công thức mà không điền. Lúc này, hàm VLOOKUP trong Excel sẽ tự động xác định tìm kiếm theo chế độ tương đối.

Khi tìm kiếm dữ liệu trên Excel theo chế độ tương đối, hàm VLOOKUP sẽ giả định bảng tính đã được sắp xếp theo thứ tự từ nhỏ đến lớn, dựa vào đó để tiến hành tìm kiếm nhị phân. Trong quá trình tìm kiếm, nếu hàm VLOOKUP phát hiện ra một dòng giá trị khớp hoàn toàn với giá trị tra cứu, hàm sẽ trả về kết quả từ dòng dữ liệu đó. Tuy nhiên, nếu hàm tìm kiếm trong Excel bắt gặp một giá trị lớn hơn giá trị tham chiếu, hàm sẽ trả về giá trị từ các dòng trước đó.

Chế độ tìm kiếm mặc định này của hàm VLOOKUP chính là nguyên nhân dẫn đến rất nhiều trường hợp sai lệch kết quả được phản ánh từ người dùng. Do đó, nếu bạn không muốn nộp một bản báo cáo với số liệu sai hết chỗ này đến chỗ khác, thì tốt hơn hết là cẩn thận với cách dùng hàm VLOOKUP nhé.

Bạn có thể bắt buộc hàm VLOOKUP tìm kiếm giá trị chính xác

Vậy phải cẩn thận như thế nào để tránh sai lệch dữ liệu tham chiếu? Khi sử dụng hàm VLOOKUP, hãy điền FALSE hoặc 0 vào tham số range_lookup để Excel hiểu rằng bạn cần tìm kiếm giá trị khớp chính xác với giá trị tra cứu nhé.

Như vậy, công thức hàm VLOOKUP lúc này sẽ như sau:

=VLOOKUP(lookup_value, table_array, col_index_ num, FALSE)

hoặc

=VLOOKUP(lookup_value, table_array, col_index_ num, 0)

Khi áp dụng cách dùng hàm VLOOKUP với chế độ tìm kiếm tuyệt đối, hàm tìm kiếm trong Excel sẽ trả về lỗi #N/A nếu như không tìm được giá trị chính xác hoàn toàn. Như vậy, bạn có thể biết được dữ liệu cần tra cứu không tồn tại trong phạm vi trang tính Excel cần tìm.

Xem thêm: Cách tìm kiếm và lọc dữ liệu không trùng nhau trong Excel

Bạn có thể yêu cầu hàm VLOOKUP tìm kiếm giá trị tương đối

Để tìm kiếm tương đối với hàm VLOOKUP, chúng ta sẽ điền TRUE hoặc 1 vào tham số range_lookup. Tất nhiên, bạn cũng có thể bỏ qua tham số này vì hàm VLOOKUP đã mặc định tìm kiếm tương đối.

Như vậy, công thức hàm VLOOKUP tìm kiếm tương đối được viết như sau:

=VLOOKUP(lookup_value, table_array, col_index_ num)

hoặc

=VLOOKUP(lookup_value, table_array, col_index_ num, TRUE)

hoặc

=VLOOKUP(lookup_value, table_array, col_index_ num, 1)

Mặc dù bạn không cần thiết phải điền tham số range_lookup nếu muốn sử dụng hàm VLOOKUP để tìm kiếm tương đối, lời khuyên dành cho bạn vẫn là thiết lập tham số này như một tham số bắt buộc. Lý do là vì thao tác này sẽ giúp bạn hình thành một thói quen thiết lập đầy đủ các tham số và đảm bảo kết quả hàm đúng như mong đợi.

Dữ liệu phải được sắp xếp khi hàm VLOOKUP tìm kiếm tương đối

Một điều vô cùng quan trọng bạn cần ghi nhớ khi sử dụng hàm VLOOKUP tìm kiếm giá trị tương đối chính là sắp xếp bảng dữ liệu theo thứ tự giá trị lớn dần. Nếu không thì khả năng cao bạn sẽ nhận được một kết quả sai lệch đấy!

Xem thêm: Hướng dẫn cách dùng chức năng sắp xếp dữ liệu - Sort trong Excel

Tổng kết

Trên đây là 10 điều đầu tiên trong danh sách 23 điều bạn nhất định phải biết khi sử dụng hàm VLOOKUP. Điều quan trọng bạn cần ghi nhớ chính là việc thiết lập các tham số hàm VLOOKUP trong Excel, dù là tìm kiếm tuyệt đối hay tìm kiếm tương đối. Ngoài ra, bạn cần chắc chắn rằng phạm vi dữ liệu trong tham số table_array chứa giá trị tra cứu ở cột đầu tiên, và giá trị cần trả về nằm ở bên phải giá trị tra cứu.

Chúng ta sẽ tiếp tục tìm hiểu các điểm đặc biệt của hàm VLOOKUP trong các bài viết tiếp theo. Trong thời gian chờ đợi, bạn hãy tham khảo thêm các bài viết về chủ đề Excel trên blog Gitiho để tích lũy kiến thức sử dụng phần mềm này nhé!

Gitiho xin cảm ơn bạn đọc và hẹn gặp lại bạn trong các bài viết tiếp theo!

Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản

Thảo luận 0 câu trả lời
Lượt xem 6831 lượt xem
Vỗ tay vỗ tay

0 Bình luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội