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

Trong bài viết trước, chúng ta đã khám phá 9 bí mật của hàm VLOOKUP trong Excel. Vậy thì ngày hôm nay, bạn hãy tiếp tục cùng Gitiho tìm hiểu những điểm nhất định cần phải biết về hàm tham chiếu phổ biến hàng đầu Excel này nhé.

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

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

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

Hàm VLOOKUP có thể gộp dữ liệu từ nhiều bảng Excel

Một trong những ứng dụng của hàm VLOOKUP là gộp dữ liệu từ hai hoặc nhiều bảng Excel. Giả sử bạn có một bảng tổng hợp thông tin đơn hàng (bảng 1) và một bảng tổng hợp thông tin sản phẩm (bảng 2). Bây giờ bạn cần điền đơn giá trên bảng 2 vào bảng 1.

Để tính được tổng tiền của các đơn hàng trong bảng 1, chúng ta cần điền đơn giá của các sản phẩm tương ứng vào cột Đơn giá (cột D). Tất nhiên, chúng ta sẽ không phải thực hiện thao tác này thủ công, mà sẽ sử dụng hàm VLOOKUP.

Tại ô D2, chúng ta sẽ điền công thức hàm VLOOKUP trong Excel như sau:

=VLOOKUP(C3,$J$3:$L$6,2)

• C3 - Ô tính Excel chứa sản phẩm cần tìm đơn giá
• $J$3:$J$6 - Phạm vi bảng 2 (trừ cột Mã sản phẩm)
• 2 - Số thứ tự của cột chứa đơn giá

Hàm VLOOKUP có thể phân loại dữ liệu trên Excel

Có thể bạn chưa biết đến tính năng cực kì thú vị này của hàm VLOOKUP. Nếu bạn cần điền dữ liệu vào các ô tính Excel dựa vào một danh mục dữ liệu có sẵn, tất cả những gì bạn cần là sử dụng hàm VLOOKUP để phân nhóm các đầu mục.

Giả sử mình có một bảng điểm như trong hình dưới đây. Công việc mình cần làm là phân loại các đầu điểm số thành điểm chữ A, B, C tương ứng.

Trước khi dùng hàm VLOOKUP, mình sẽ thực hiện một thao tác bổ sung.

Để thuận tiện cho thao tác điền dữ liệu vào bảng chính trong trường hợp thêm mới các dòng dữ liệu phía dưới, mình sẽ đặt tên cho bảng phụ quy đổi điểm.

Thao tác đặt tên cho bảng Excel vô cùng đơn giản, bạn chỉ cần chọn vùng dữ liệu cần đặt tên, sau đó điền tên vào ô hiển thị vị trí ô tính như trong hình dưới đây.

Trong đó:

• B2 - Ô tính Excel chứa giá trị cần tìm
• điểm - Bảng phụ quy đổi điểm
• 2 - Số thứ tự của cột Điểm chữ trong bảng Điểm (cột F)
• TRUE - Hàm VLOOKUP tìm kiếm tương đối

Như đã nói trong bài viết trước, với cách dùng hàm VLOOKUP ở chế độ tìm kiếm tương đối, bạn bắt buộc phải sắp xếp dữ liệu theo thứ tự tăng dần hoặc từ A-Z.

Hàm VLOOKUP sẽ trả về kết quả là một bảng tính đã được quy đổi từ điểm số ra đúng điểm chữ.

Các mẹo nâng cấp hàm VLOOKUP

Tham chiếu tuyệt đối giúp hàm VLOOKUP linh hoạt hơn

Nếu bạn muốn dùng một công thức hàm VLOOKUP cho nhiều hơn một ô tính hoặc các phạm vi ô tính rời rạc, cách tốt nhất là sử dụng tham chiếu tuyệt đối cho tham số table_array (và lookup_value nếu cần thiết).

Tham chiếu tuyệt đối sẽ giúp bạn copy toàn bộ hàm VLOOKUP, sau đó bạn chỉ cần điều chỉnh col_index_num để lấy đúng giá trị từ cột mình cần mà không làm sai lệch phạm vi cần tham chiếu.

Mình có một thanh tìm kiếm thông tin nhân sự sử dụng hàm VLOOKUP như trong hình dưới đây.

Giả sử mình cần tìm thông tin của nhân sự ID 710. Lúc này, mình sẽ điền "710" vào ô ID (ô A2). Sau đó, hàm VLOOKUP sẽ giúp mình điền dữ liệu vào tất cả các ô còn lại.

Tại ô B2, mình điền công thức hàm VLOOKUP trong Excel như sau:

=VLOOKUP($A$2,$A$5:$E$20,2)

• $A$2 - Ô tính Excel chứa ID cần tra cứu thông tin
• $A$5:$E$20 - Phạm vi bảng thông tin nhân sự
• 2 - Số thứ tự cột Họ và tên trong bảng thông tin nhân sự (cột B)

Đặt tên vùng dữ liệu giúp hàm VLOOKUP dễ đọc hơn

Thay vì sử dụng tham chiếu tuyệt đối, một mẹo khác để cải thiện công thức hàm VLOOKUP của bạn là đặt tên cho các vùng dữ liệu, đồng thời giúp bạn dễ dàng nắm bắt được nội dung của các vùng dữ liệu trong trường hợp trang tính Excel của bạn có quá nhiều phần nội dung.

Với ví dụ về thanh tra cứu thông tin nhân viên ở trên, mình sẽ đặt tên cho các ô tính và phạm vi ô tính:

• "Info" - Bảng thông tin nhân viên
• "ID" - Ô B2 chứa ID cần tra cứu

Lúc này, hàm VLOOKUP của mình trong ô B2 sẽ trở thành:

=VLOOKUP(id,info,2)

• id - Ô tính Excel chứa ID cần tra cứu thông tin
• info - Phạm vi bảng thông tin nhân sự
• 2 - Số thứ tự cột Họ và tên trong bảng thông tin nhân sự (cột B)

Việc đặt tên cho các vùng dữ liệu khi dùng hàm VLOOKUP sẽ đồng nghĩa với việc sử dụng tham chiếu tuyệt đối, vì các phạm vi dữ liệu được đặt tên sẽ được Excel mặc định là tham chiếu tuyệt đối.

Xem thêm: Cách tạo ô lọc dữ liệu trong Excel hiện kết quả theo thời gian thực

Thêm cột mới có thể làm hỏng công thức hàm VLOOKUP

Nếu bạn đã có một hàm VLOOKUP trên trang tính, bạn sẽ nhận thấy công thức ngừng hoạt động ngay khi thêm một cột mới. Nguyên nhân dẫn đến sự cố này là vì hàm VLOOKUP không thể tự động điều chỉnh tham số col_index_num khi thêm hoặc bớt cột trên trang tính.

Để tránh sự cố này với hàm VLOOKUP, thay vì điền một giá trị số vào tham số col_index_num, bạn hãy sử dụng một công thức hàm. Cách làm như thế nào? Mình sẽ hướng dẫn bạn ngay dưới đây,

Kết hợp hàm ROW hoặc hàm COLUMN để cải thiện hàm VLOOKUP

Cách đầu tiên, bạn có thể sử dụng hàm ROW hoặc hàm COLUMN cho tham số index_col_num. Trong trường hợp bạn cần lấy dữ liệu từ các cột liền kề, mẹo này sẽ giúp bạn có được một hàm tham chiếu mạnh mẽ, và mình đảm bảo rằng nó sẽ hoạt động ngay cả khi bạn copy công thức sang các cột khác.

Với ví dụ về thanh tra cứu ID phía trên, thay vì điền giá trị số vào tham số col_index_num, mình sẽ sử dụng hàm COLUMN:

COLUMN()

Với công thức hàm VLOOKUP này, bạn chỉ cần copy paste hàm sang các ô khác trong thanh tra cứu mà không cần thực hiện bất kỳ thay đổi gì. Khi thêm mới một cột ở giữa bảng, chúng ta cũng không gặp phải bất kỳ vấn đề nào.

Xem thêm: Hướng dẫn hàm COLUMN trả về vị trí cột của tham chiếu trong Excel

Kết hợp hàm MATCH để cải thiện hàm VLOOKUP

Hàm MATCH vốn được biết là trở thủ đắc lực nhất của các hàm tham chiếu trong Excel, tất nhiên bao gồm cả hàm VLOOKUP. Khi sử dụng hàm cùng hàm MATCH, hàm VLOOKUP được nâng lên một tầm cao mới khi có thể tìm kiếm theo cả hàng và cột.

Nếu bạn vẫn còn mơ hồ về sự kết hợp hoàn hảo này thì hãy theo dõi ví dụ dưới đây. Mình có một bảng tổng hợp doanh thu của 7 sản phẩm trong các tháng đầu năm. 

Yêu cầu lúc này là tìm doanh thu của một sản phẩm cụ thể trong một tháng cụ thể như trong bảng phụ bên phải.

Hàm VLOOKUP thông thường chỉ có thể tìm kiếm dựa theo một giá trị thôi, vậy thì yêu cầu trên là không thể. Tuy nhiên, mình sẽ chỉ cho bạn cách biến điều không thể thành có thể bằng hàm MATCH.

Công thức hàm VLOOKUP mình sử dụng như sau:

=VLOOKUP(G2,A2:D6,MATCH(G3,A1:D1,0),0)

Trong đó:

• G2 - Ô tính Excel chứa giá trị cần tra cứu
• A2:D6 - Phạm vi dữ liệu cần tham chiếu
• MATCH(G3,A1:D1,0) - Công thức xác định vị trí cụ thể của ô tính cần tham chiếu
• 0 - Hàm VLOOKUP tham chiếu tuyệt đối

Bằng cách kết hợp hàm MATCH, chúng ta đã giúp hàm VLOOKUP tìm được vị trí chính xác của giá trị cần trả về trong bảng dữ liệu tổng hợp.

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

Tổng kết

Qua bài viết ngày hôm nay, chúng ta đã khám phá thêm các bí mật của hàm VLOOKUP. Có lẽ một vài điểm trong bài bạn chỉ mới biết một vài phút trước đúng không nào? Tuy nhiên, chủ đề này vẫn sẽ chưa dừng lại ở đây.

Hãy cùng theo dõi các bài viết tiếp theo để cùng Gitiho tìm hiểu tất tần tật về hàm VLOOKUP nhé. Trong thời gian chờ đợi, một lựa chọn dành cho bạn là tham khảo thêm các bài viết về chủ đề Excel trên blog Gitiho để tích lũy các thủ thuật hữu ích khi làm việc trên Excel.

Ngoài ra, bạn có thể tham khảo khóa học Tuyệt đỉnh Excel của chúng mình. Khóa học sẽ cung cấp cho bạn tất cả kiến thức Excel cơ bản bạn cần biết khi làm việc văn phòng. Không chỉ vậy, nếu bạn có bất kỳ câu hỏi nào trong quá trình học, bạn chỉ cần để lại bình luận và giảng viên sẽ hỗ trợ trong vòng 24 giờ. Cùng tham gia lớp học Excel của Gitiho và chinh phục các đỉnh cao Excel nhé.

Gitiho xin cảm ơn bạn đọc và chúc bạn thành công!