[Excel Tutorial] Hướng dẫn hàm VLOOKUP và HLOOKUP

Hàm VLOOKUP và HLOOKUP là hai hàm Excel điển hình, phổ biến và mạnh mẽ bậc nhất trong Excel giúp người dùng tra cứu, trích xuất dữ liệu nhanh chóng và chính xác. Trong xử lý dữ liệu hàng ngày, việc làm chủ hai công cụ này sẽ giúp bạn tiết kiệm hàng giờ thao tác thủ công đồng thời tối ưu hóa quy trình báo cáo và hạn chế tối đa sai sót. Bài viết này, Starttrain sẽ hướng dẫn chi tiết từ khái niệm, cú pháp, ví dụ minh họa thực tế, cách kết hợp và các lỗi phổ biến khi dùng hai hàm này.

Hàm VLOOKUP và HLOOKUP là hàm gì?

Hàm VLOOKUP và HLOOKUP là hai hàm tìm kiếm dữ liệu theo điều kiện trong Excel giúp dò tìm một giá trị ở một bảng tham chiếu và trả về giá trị tương ứng ở cột hoặc dòng khác. Điểm khác biệt chính nằm ở hướng tìm kiếm của hai hàm:

• Hàm VLOOKUP: Tìm kiếm dữ liệu theo chiều dọc. Hàm sẽ dò giá trị từ trên xuống dưới ở cột đầu tiên của bảng dữ liệu, sau đó trả về giá trị ở cột tương ứng cùng hàng.
• Hàm HLOOKUP: Tìm kiếm dữ liệu theo chiều ngang. Hàm sẽ dò giá trị từ trái sang phải ở hàng đầu tiên của bảng dữ liệu, sau đó trả về giá trị ở hàng tương ứng cùng cột.

Cú pháp hàm VLOOKUP

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

Trong đó:

• lookup_value (Bắt buộc): Giá trị cần tìm kiếm.
• table_array (Bắt buộc): Bảng chứa dữ liệu cần dò tìm. Cột đầu tiên của bảng này phải chứa giá trị cần tìm (lookup_value). Nên cố định bảng này bằng phím F4.
• col_index_num (Bắt buộc): Thứ tự của cột chứa dữ liệu cần lấy ra, tính từ cột đầu tiên của table_array (cột đầu tiên tính là 1).
• range_lookup (Tùy chọn): Kiểu tìm kiếm.
  • Nhập 0 (hoặc FALSE): Tìm kiếm chính xác 100%.
  • Nhập 1 (hoặc TRUE): Tìm kiếm tương đối (gần đúng).

Cú pháp hàm HLOOKUP

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Giải thích chi tiết các tham số:

• lookup_value (Bắt buộc): Giá trị cần tìm kiếm.
• table_array (Bắt buộc): Bảng dữ liệu tham chiếu. Dòng đầu tiên của bảng phải chứa giá trị cần tìm.
• row_index_num (Bắt buộc): Thứ tự của dòng chứa dữ liệu cần trả về, tính từ dòng đầu tiên của table_array.
• range_lookup (Tùy chọn): Nhập 0 (FALSE) để tìm chính xác hoặc 1 (TRUE) để tìm tương đối.

Minh họa thực tế cách dùng hàm VLOOKUP và hàm HLOOKUP

Có hai bảng dữ liệu:

• Bảng dọc (A1:D11): Bảng chi tiết sản phẩm gồm các cột Mã SP, Tên SP, Đơn giá, Danh mục.
• Bảng ngang (A13:F15): Bảng báo cáo doanh số gồm các dòng Chỉ tiêu, Danh mục, Số lượng bán tương ứng với từng Mã SP (P001, P002, P003,…).

Yêu cầu: Dựa vào Mã SP nhập ở ô I2, J2, K2, tự động điền thông tin:

• Tên SP
• Danh mục
• Số lượng bán

Điền tên sản phẩm bằng hàm VLOOKUP

Tại ô I3, nhập công thức =VLOOKUP(I$2,$A$1:$D$11,2,0)

Trong đó I$2 là giá trị cần tra (Mã SP P001), $A$1:$D$11 là bảng dữ liệu dọc được cố định tuyệt đối bằng dấu $, số 2 tượng trưng cho cột “Tên SP” (cột thứ 2 trong bảng) và 0 thể hiện kiểu tìm kiếm chính xác.

Nhấn Enter, ô I3 trả về kết quả là “Laptop”, tương ứng chính xác với mã P001 trong bảng dữ liệu gốc

Điền danh mục sản phẩm bằng hàm VLOOKUP

Tại ô I4, nhập công thức =VLOOKUP(I$2,$A$1:$D$11,4,0). Công thức này tương tự như bước 2 nhưng chỉ số cột đổi thành 4 để lấy dữ liệu từ cột “Danh mục”. Kết quả trả về tại ô I4 là “Điện tử”.

Điền số lượng bán bằng hàm HLOOKUP

Do thông tin số lượng bán nằm ở bảng ngang (A13:F15), tại ô I5 sử dụng công thức =HLOOKUP(I$2,$A$13:$F$15,3,0). Tham số I$2 là mã P001, bảng tham chiếu là $A$13:$F$15, chỉ số dòng là 3 và 0 để tìm chính xác.

Copy công thức cho các mã sản phẩm còn lại

Kéo con trỏ Fill Handle từ ô I5 sang các ô J5 và K5. Kết quả số lượng bán tương ứng cho P001, P002, P003 lần lượt hiển thị là 120, 350, và 210.

Kiểm tra tính linh hoạt của công thức

Khi thay đổi ô K2 từ mã P003 thành mã P004, toàn bộ các ô kết quả phía dưới tự động cập nhật: Tên SP chuyển thành “Tai nghe”, Danh mục thành “Âm thanh”, và Số lượng bán là 580. Điều này chứng tỏ công thức hoạt động hoàn toàn chính xác và linh hoạt.

Khi nào nên dùng hàm VLOOKUP và HLOOKUP?

VLOOKUP

Bạn nên ưu tiên sử dụng VLOOKUP trong các trường hợp sau:

• Bảng dữ liệu chính được tổ chức theo các cột. Tiêu đề bảng nằm ở dòng đầu tiên (ví dụ: Mã SP, Tên SP, Đơn giá, Danh mục nằm ở các cột A, B, C, D).
• Khóa tìm kiếm nằm ở cột ngoài cùng bên trái: Mã định danh (ID, Mã nhân viên, Mã hàng) nằm ở cột thứ nhất của bảng và thông tin cần lấy nằm ở các cột phía bên phải.
• Tần suất cập nhật dữ liệu theo hàng: Dữ liệu mới được thêm vào bằng cách chèn thêm các hàng mới bên dưới. Đây là định dạng chuẩn của 90% cơ sở dữ liệu hiện nay.

HLOOKUP

Bạn nên sử dụng HLOOKUP trong các trường hợp sau:

• Cấu trúc dữ liệu dạng bảng ngang (Horizontal Data): Bảng dữ liệu chính được tổ chức theo các dòng. Tiêu đề chỉ tiêu nằm ở cột đầu tiên bên trái (ví dụ: Chỉ tiêu, Danh mục, Số lượng bán nằm ở các dòng 13, 14, 15).
• Khóa tìm kiếm nằm ở dòng trên cùng: Mã định danh hoặc mốc thời gian (Tháng 1, Tháng 2, Mã SP P001, P002,…) nằm ở hàng đầu tiên của bảng tham chiếu.
• Báo cáo định dạng theo chu kỳ thời gian: Bảng dữ liệu phát triển theo chiều ngang (thêm cột mới khi có thêm thời gian hoặc sản phẩm mới).

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

Trong thực tế xử lý dữ liệu phức tạp, sẽ có một số tình huống cần kết hợp cả hai hàm hoặc dùng lồng ghép nhau để giải quyết bài toán:

• Trường hợp tổng hợp dữ liệu từ hai nguồn có cấu trúc trái ngược nhau
  • Ví dụ khi cần lập bảng tổng hợp doanh số và thông tin sản phẩm. Tuy nhiên, bảng thông tin sản phẩm lại thiết kế theo chiều dọc, còn bảng doanh số lại lưu theo chiều ngang.
  • Dùng VLOOKUP để truy xuất Tên SP, Đơn giá từ bảng dọc và dùng HLOOKUP trên cùng bảng tổng hợp để truy xuất Số lượng bán từ bảng ngang.
• Trường hợp tra cứu hai chiều trên bảng ma trận
  • Khi muốn tìm doanh số của một Mã SP (theo hàng) trong một Tháng cụ thể (theo cột).
  • Bạn có thể lồng hàm HLOOKUP vào bên trong vị trí tham số col_index_num của VLOOKUP (hoặc kết hợp VLOOKUP với MATCH/HLOOKUP) để xác định vị trí cột/dòng động một cách tự động khi điều kiện thay đổi.

Các lỗi thường gặp khi dùng VLOOKUP và HLOOKUP

Lỗi #N/A

Lỗi #N/A xuất hiện khi Excel đã duyệt toàn bộ cột đầu tiên (hoặc hàng đầu tiên) nhưng không tìm thấy giá trị tra cứu. Nguyên nhân thường xuất phát từ việc lookup_value không tồn tại trong dữ liệu gốc, hoặc do kiểu dữ liệu không khớp. Để khắc phục, bạn cần kiểm tra lại dữ liệu gốc. Nếu vấn đề do khoảng trắng dư thừa, hãy kết hợp hàm TRIM theo dạng =VLOOKUP(TRIM(I2), $A$1:$D$11, 2, 0). Để bảng tính chuyên nghiệp hơn có thể bọc toàn bộ công thức bằng hàm IFERROR, ví dụ: =IFERROR(VLOOKUP(I2, $A$1:$D$11, 2, 0), “Không tìm thấy”).

Lỗi #VALUE!

Khi nhận được báo lỗi #VALUE!, Excel đang cảnh báo rằng tham số bạn truyền vào công thức bị vi phạm quy định về mặt cú pháp toán học. Trong ngữ cảnh của hàm VLOOKUP hay HLOOKUP, lỗi này hầu hết xảy ra khi bạn vô tình nhập chỉ số cột col_index_num hoặc chỉ số dòng row_index_num nhỏ hơn 1, hoặc do nhập sai định dạng ký tự phân cách giữa các tham số.

Hãy kiểm tra lại vị trí tham số thứ 3 trong công thức và đảm bảo đây phải là một số nguyên dương từ 1 trở lên. Đồng thời rà soát lại các dấu ngoặc đóng mở cũng như dấu phẩy hoặc dấu chấm phẩy phân cách tham số.

Lỗi #REF!

Lỗi #REF! đại diện cho sự đứt gãy vùng tham chiếu trong Excel. Khi sử dụng hai hàm tìm kiếm này, bạn sẽ gặp phải lỗi #REF! nếu chỉ số cột/dòng cần lấy dữ liệu lớn hơn tổng số cột/dòng có trong bảng dữ liệu chỉ định (table_array). Một trường hợp khác là do cột hoặc hàng tham chiếu ban đầu đã bị xóa mất khỏi trang tính.

Để giải quyết, bạn chỉ cần mở rộng phạm vi quét dữ liệu của table_array bao phủ đủ các cột cần lấy, hoặc điều chỉnh lại chỉ số col_index_num cho tương thích với quy mô thực tế của bảng. Chẳng hạn, nếu bảng table_array quét từ cột A đến C (gồm 3 cột) thì chỉ số lấy dữ liệu tối đa chỉ có thể là 3.

Lỗi quên cố định địa chỉ tham chiếu

Lúc này Excel không đưa ra bất kỳ thông báo lỗi nào. Khi bạn sao chép công thức xuống các ô bên dưới hoặc sang các ô bên phải, vùng dữ liệu tham chiếu bị trôi theo tương đối, dẫn đến việc Excel tra cứu sai vùng dữ liệu cần thiết và trả về kết quả không chính xác. Giải pháp bắt buộc cho trường hợp này là luôn sử dụng địa chỉ tuyệt đối cho vùng table_array. Ngay sau khi chọn vùng dữ liệu bằng chuột, bạn hãy nhấn phím F4 (hoặc Fn + F4) để thêm ký tự $ trước chữ cái chỉ cột và số chỉ hàng.

[FAQ] Một số câu hỏi thường gặp về Hàm VLOOKUP và HLOOKUP

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

Không. Cả hai hàm đều không phân biệt chữ hoa hay chữ thường. Ví dụ: “p001” và “P001” được hiểu là giống nhau.

Tại sao công thức VLOOKUP đúng nhưng vẫn trả về lỗi #N/A? 

Lỗi này thường xảy ra do hai nguyên nhân chính: mã cần tìm bị dính khoảng trắng ở cuối (ví dụ “P001 “), hoặc định dạng dữ liệu ở hai bảng không đồng nhất (một bên là chuỗi văn bản, một bên là dạng số). Bạn có thể xử lý bằng cách kiểm tra lại định dạng ô hoặc dùng hàm TRIM().

Làm thế nào để cố định vùng dữ liệu table_array trong công thức? 

Bạn quét chọn vùng table_array sau đó nhấn phím F4 trên bàn phím (hoặc Fn + F4). Ký hiệu $ sẽ xuất hiện trước chữ cái cột và số hàng.

Tổng kết

Việc nắm vững hàm VLOOKUP và HLOOKUP là nền tảng quan trọng giúp bạn xử lý, tra cứu và tổng hợp dữ liệu trên Excel một cách chuyên nghiệp và tiết kiệm thời gian. Dù hiện nay có nhiều giải pháp thay thế tiên tiến hơn như XLOOKUP hay INDEX & MATCH, hai hàm này vẫn giữ vai trò cốt lõi và được ứng dụng rộng rãi trong hầu hết các công việc văn phòng hàng ngày. Hãy áp dụng ngay các ví dụ minh họa và lưu ý xử lý lỗi trong bài viết để tối ưu hóa hiệu suất làm việc của bạn.

Việc làm chủ hàm VLOOKUP và HLOOKUP là nền tảng quan trọng giúp bạn xử lý và tra cứu dữ liệu nhanh chóng. Để tiếp tục khám phá các hàm Excel nâng cao và mẹo Excel thực tế, bạn có thể tham khảo Thư viện tự học Phân tích Dữ liệu miễn phí hoặc đăng ký Khóa học Excel nâng cao và phân tích dữ liệu để tối ưu hóa trọn bộ quy trình báo cáo chuyên nghiệp.