Bài Tập Excel Vlookup Và Hlookup: Hướng Dẫn Chi Tiết, Ví Dụ Thực Tế Và Bộ Đề Luyện Tập Nâng Cao (1500+ Từ)

Có thể bạn quan tâm: Cách Đính Kèm Tập Tin Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

Mở đầu: Tại sao VLOOKUP và HLOOKUP lại quan trọng?

Trong môi trường làm việc hiện đại, việc xử lý dữ liệu nhanh chóng, chính xác là một kỹ năng không thể thiếu. Microsoft Excel – một trong những công cụ bảng tính phổ biến nhất – cung cấp cho người dùng hàng loạt hàm mạnh mẽ, trong đó VLOOKUP (Vertical Lookup) và HLOOKUP (Horizontal Lookup) là hai hàm được sử dụng rộng rãi nhất để tra cứu dữ liệu.

• VLOOKUP giúp bạn tìm kiếm một giá trị trong cột đầu tiên của một bảng và trả về một giá trị tương ứng trong cùng hàng nhưng ở cột khác.
• HLOOKUP thực hiện việc tương tự nhưng theo chiều ngang: tìm giá trị trong hàng đầu tiên và trả về giá trị trong cùng cột nhưng ở hàng khác.

Hai hàm này không chỉ giúp giảm thời gian nhập liệu mà còn giảm thiểu lỗi do nhập tay, đồng thời tạo nền tảng cho việc xây dựng các báo cáo, dashboard phức tạp. Vì vậy, nắm vững VLOOKUP và HLOOKUP là mục tiêu quan trọng đối với mọi người dùng Excel, từ sinh viên, nhân viên văn phòng tới nhà quản lý cấp cao.

Trong bài viết này, chúng ta sẽ:

1. Giải thích chi tiết cú pháp và nguyên lý hoạt động của VLOOKUP và HLOOKUP.
2. Đưa ra các lưu ý, lỗi thường gặp và cách khắc phục.
3. Xây dựng bộ đề bài tập thực tế từ cơ bản đến nâng cao, kèm đáp án và giải thích chi tiết.
4. Giới thiệu các hàm thay thế (XLOOKUP, INDEX-MATCH) và cách chuyển đổi khi cần.

Hãy cùng đi sâu vào thế giới tra cứu dữ liệu trong Excel!

Có thể bạn quan tâm: Add‑in Trong Excel 2026: Hướng Dẫn Chi Tiết Từ Cài Đặt Đến Tối Ưu Hoá Hiệu Suất

1. Cú pháp và nguyên lý hoạt động

1.1. VLOOKUP

Cú pháp:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Nguyên lý: Excel sẽ duyệt cột đầu tiên của table_array để tìm lookup_value. Khi tìm thấy, nó sẽ lấy giá trị ở cùng hàng, ở cột thứ col_index_num.

Lưu ý: Cột chứa lookup_value phải là cột đầu tiên của table_array. Nếu không, VLOOKUP sẽ trả về lỗi #REF! hoặc #N/A.

1.2. HLOOKUP

Cú pháp:

HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

Nguyên lý: Excel sẽ duyệt hàng đầu tiên của table_array để tìm lookup_value. Khi tìm thấy, nó sẽ lấy giá trị ở cùng cột, ở hàng thứ row_index_num.

Lưu ý: Hàng chứa lookup_value phải là hàng đầu tiên của table_array.

Có thể bạn quan tâm: Cách Thêm Gantt Chart Vào Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Các lưu ý, lỗi thường gặp và cách khắc phục

2.1. Lỗi #N/A – Không tìm thấy giá trị

• Nguyên nhân: lookup_value không tồn tại trong cột/hàng đầu tiên của table_array.
• Khắc phục:
• Kiểm tra lại dữ liệu nguồn, loại bỏ khoảng trắng thừa (TRIM).
• Đảm bảo định dạng dữ liệu thống nhất (số vs. văn bản).
• Sử dụng IFERROR(VLOOKUP(...), "Không tìm thấy") để hiển thị thông báo thân thiện.

2.2. Lỗi #REF! – Số cột/hàng trả về vượt quá phạm vi

• Nguyên nhân: col_index_num (hoặc row_index_num) lớn hơn số cột (hàng) trong table_array.
• Khắc phục: Kiểm tra lại giá trị chỉ số, hoặc mở rộng table_array để bao gồm cột/hàng cần trả về.

2.3. Lỗi #VALUE! – Đối số không hợp lệ

• Nguyên nhân: col_index_num hoặc row_index_num không phải là số nguyên dương.
• Khắc phục: Đảm bảo các đối số là số nguyên > 0.

2.4. Tìm kiếm gần đúng vs chính xác

• Khi range_lookup = TRUE (hoặc bỏ qua), Excel yêu cầu cột/hàng đầu tiên được sắp xếp tăng dần. Nếu không, kết quả có thể sai.
• Khi range_lookup = FALSE, Excel sẽ tìm kiếm chính xác, không yêu cầu sắp xếp.
• Khuyến nghị: Hầu hết các trường hợp thực tế nên dùng FALSE để tránh lỗi do dữ liệu chưa sắp xếp.

2.5. Vấn đề case‑sensitivity (phân biệt chữ hoa/thường)

VLOOKUP/HLOOKUP không phân biệt chữ hoa/thường. Nếu cần phân biệt, có thể kết hợp với EXACT trong một công thức mảng hoặc dùng hàm XLOOKUP (Excel 365/2026).

2.6. Khi cần tra cứu nhiều cột cùng một giá trị

VLOOKUP chỉ trả về một cột duy nhất. Để lấy nhiều cột, bạn có thể:

• Lồng nhiều VLOOKUP trong các ô khác nhau, hoặc
• Sử dụng INDEX + MATCH (linh hoạt hơn), hoặc
• Dùng XLOOKUP (được giới thiệu trong Office 365) có thể trả về một mảng.

3. Bộ đề bài tập thực tế

Cách làm: Đối với mỗi bài, hãy tự tạo bảng dữ liệu trong Excel, áp dụng công thức VLOOKUP hoặc HLOOKUP, sau đó kiểm tra kết quả. Đáp án và giải thích chi tiết được cung cấp ở cuối mỗi mục.

Bài 1: Tra cứu giá sản phẩm (cơ bản)

Dữ liệu:

Yêu cầu: Cho một ô nhập mã sản phẩm (ví dụ ô E2), sử dụng VLOOKUP để trả về Tên SP và Đơn giá tương ứng.

Công thức:

• Tên SP: =VLOOKUP(E2, $A$2:$C$5, 2, FALSE)
• Đơn giá: =VLOOKUP(E2, $A$2:$C$5, 3, FALSE)

Giải thích: table_array là $A$2:$C$5, cột chỉ số 2 và 3 trả về tên và giá.

Bài 2: Tra cứu điểm sinh viên (học kỳ 1) và tính trung bình (nâng cao)

Dữ liệu:

Yêu cầu:
1. Nhập mã sinh viên ở ô E2.
2. Dùng HLOOKUP để lấy điểm Toán, Văn, Anh.
3. Tính trung bình điểm 3 môn.

Công thức:

• Toán: =HLOOKUP($E$2, $A$1:$D$5, 2, FALSE)
• Văn: =HLOOKUP($E$2, $A$1:$D$5, 3, FALSE)
• Anh: =HLOOKUP($E$2, $A$1:$D$5, 4, FALSE)
• Trung bình: =AVERAGE(F2:H2) (giả sử F2, G2, H2 chứa các điểm vừa lấy)

Giải thích: Vì dữ liệu được sắp xếp theo cột (mỗi môn là một hàng), chúng ta dùng HLOOKUP. row_index_num 2,3,4 tương ứng với các hàng điểm.

Bài 3: Tra cứu giá trị theo ngày (định dạng ngày)

Dữ liệu bán hàng:

Yêu cầu: Nhập ngày bất kỳ ở ô E2 (định dạng ngày). Dùng VLOOKUP để trả về doanh thu.

Công thức: =VLOOKUP(E2, $A$2:$B$5, 2, FALSE)

Lưu ý: Đảm bảo ô E2 và cột A đều có định dạng ngày giống nhau; nếu không, VLOOKUP sẽ trả #N/A.

Bài 4: Tra cứu đa tiêu chí (kết hợp VLOOKUP và CONCATENATE)

Có thể bạn quan tâm: Tự Động Điều Chỉnh Độ Rộng Cột (autofit Column Width) Trong Excel Bằng Vba: Hướng Dẫn Chi Tiết Và Thực Hành

Dữ liệu:

Yêu cầu: Nhập Mã NV và Tháng ở ô E2 và F2. Dùng VLOOKUP để lấy Lương.

Bước 1: Tạo cột trợ giúp (cột D) trong bảng dữ liệu: =A2&"_"&B2 → sẽ cho ra “NV01_1”, “NV01_2”, …

Bước 2: Tạo giá trị tra cứu: =E2&"_"&F2

Bước 3: Công thức VLOOKUP: =VLOOKUP(E2&"_"&F2, $D$2:$F$5, 3, FALSE)

Giải thích: Bằng cách ghép hai trường thành một khóa duy nhất, chúng ta có thể dùng VLOOKUP cho tra cứu đa tiêu chí.

Bài 5: Tra cứu với bảng dữ liệu lớn (10,000+ dòng) – Tối ưu hiệu suất

Mô tả: Bạn có một bảng Khách hàng gồm 12,000 dòng, cột A là Mã KH, cột C là Tên KH, cột F là Doanh thu năm. Bạn cần tra cứu tên và doanh thu dựa trên mã KH nhập vào.

Giải pháp tối ưu:

1. Sử dụng phạm vi có tên (named range) để tránh tính toán lại toàn bộ vùng khi kéo công thức.
2. Đặt tên tblKH cho $A$2:$F$12001.
3. Dùng VLOOKUP với 3 đối số cuối cùng là FALSE để tránh tìm gần đúng (nên dùng FALSE).
4. Bảo vệ dữ liệu bằng việc chuyển sang định dạng Table (Ctrl+T). Khi dữ liệu mở rộng, công thức tự cập nhật.

Công thức:

• Tên KH: =VLOOKUP(E2, tblKH, 3, FALSE)
• Doanh thu: =VLOOKUP(E2, tblKH, 6, FALSE)

Lưu ý: Khi làm việc với bảng lớn, tránh sử dụng toàn bộ cột (A:B) làm table_array vì sẽ làm chậm tính toán. Chỉ định đúng phạm vi thực tế.

Bài 6: Kết hợp VLOOKUP và IFERROR – Xử lý lỗi một cách chuyên nghiệp

Tình huống: Khi tra cứu Mã Đơn Hàng, có một số mã không tồn tại trong danh sách. Thay vì hiển thị #N/A, muốn hiển thị “Chưa có dữ liệu”.

Công thức: =IFERROR(VLOOKUP(E2, $A$2:$D$500, 4, FALSE), "Chưa có dữ liệu")

Giải thích: IFERROR bắt mọi lỗi (N/A, REF, VALUE…) và trả về thông báo tùy chỉnh.

Bài 7: Tra cứu ngang bằng HLOOKUP trong bảng kết quả kỳ thi (đảo chiều)

Dữ liệu:

Yêu cầu: Nhập Mã SV ở ô E2 (ví dụ “SV02”). Dùng HLOOKUP để trả về điểm Toán, Văn, Anh.

Công thức:

• Toán: =HLOOKUP($E$2, $A$1:$D$4, 2, FALSE)
• Văn: =HLOOKUP($E$2, $A$1:$D$4, 3, FALSE)
• Anh: =HLOOKUP($E$2, $A$1:$D$4, 4, FALSE)

Giải thích: Vì các sinh viên nằm trên hàng đầu tiên, chúng ta dùng HLOOKUP; row_index_num 2,3,4 trả về các điểm.

Bài 8: Tra cứu với dữ liệu không sắp xếp – Sử dụng FALSE

Dữ liệu (không sắp xếp):

Yêu cầu: Nhập mã sản phẩm ở ô E2, trả về Tên và Giá.

Công thức: =VLOOKUP(E2, $A$2:$C$5, 2, FALSE) và =VLOOKUP(E2, $A$2:$C$5, 3, FALSE)

Giải thích: Vì dữ liệu không được sắp xếp, chúng ta phải dùng FALSE để yêu cầu tìm kiếm chính xác, tránh lỗi trả về giá trị gần đúng.

Bài 9: Sử dụng VLOOKUP trong định dạng bảng động (Dynamic Array) – Excel 365

Mô tả: Bạn có danh sách Sản phẩm và muốn tạo một danh sách đề xuất dựa trên mã nhập.

Công thức mảng:

=LET( mã, FILTER(A2:A100, A2:A100<>""), tên, VLOOKUP(mã, $A$2:$C$100, 2, FALSE), giá, VLOOKUP(mã, $A$2:$C$100, 3, FALSE), HSTACK(mã, tên, giá)
)

Giải thích: LET giúp lưu trữ các biến tạm, FILTER loại bỏ ô trống, HSTACK ghép các cột lại thành một mảng mới. Đây là cách hiện đại để tạo bảng động mà không cần sao chép công thức xuống.

Bài 10: So sánh VLOOKUP và XLOOKUP – Khi nào nên chuyển sang XLOOKUP?

Tình huống: Bạn đang dùng VLOOKUP để tra cứu, nhưng gặp các hạn chế:

• Không thể tra cứu sang trái (cột chỉ số phải ở bên phải).
• Không trả về mảng nhiều cột cùng lúc.
• Cần xử lý lỗi phức tạp.

Giải pháp XLOOKUP (Excel 365/2026 trở lên):

=XLOOKUP(E2, $A$2:$A$100, $C$2:$C$100, "Không tìm thấy", 0, 1)

• Tham số thứ ba là phạm vi trả về (có thể ở bên trái hoặc phải).
• Tham số thứ năm 0 = tìm chính xác, -1 = tìm gần nhất (sắp xếp giảm), 1 = tìm gần nhất (tăng).
• Tham số thứ sáu 1 cho phép trả về mảng (trong Excel 365, có thể trả về nhiều cột bằng cách truyền phạm vi trả về là một mảng đa cột).

Kết luận: Khi cần linh hoạt hơn, hãy chuyển sang XLOOKUP. Tuy nhiên, VLOOKUP vẫn hữu ích trong môi trường Excel cũ (2007‑2026).

4. Các bài tập nâng cao – Kết hợp VLOOKUP với các hàm khác

4.1. VLOOKUP + SUMIF – Tính tổng doanh thu theo khách hàng

Dữ liệu:

Yêu cầu: Nhập Mã KH ở ô E2, trả về Tổng doanh thu của khách hàng đó.

Công thức: =SUMIF($A$2:$A$6, E2, $C$2:$C$6)

Kết hợp VLOOKUP: Nếu muốn đồng thời hiển thị Tên KH (có trong bảng khác), dùng VLOOKUP để lấy tên và SUMIF để tính tổng.

Tên KH: =VLOOKUP(E2, $G$2:$H$10, 2, FALSE)
Tổng: =SUMIF($A$2:$A$6, E2, $C$2:$C$6)

4.2. VLOOKUP + DATEVALUE – Tra cứu dữ liệu theo tháng

Bảng bán hàng:

Yêu cầu: Nhập Tháng (số 1‑12) ở ô E2, trả về tổng số lượng của mã SP A001 trong tháng đó.

Công thức:

=SUMIFS($C$2:$C$5, $B$2:$B$5, "A001", MONTH($A$2:$A$5), E2)

Ở đây VLOOKUP không cần, nhưng nếu muốn lấy Tên SP từ bảng khác, ta dùng VLOOKUP:

Tên SP: =VLOOKUP("A001", $F$2:$G$10, 2, FALSE)

4.3. VLOOKUP trong PivotTable – Tự động cập nhật

Bạn có một bảng Sales và một bảng Products. Khi tạo PivotTable để tổng hợp doanh thu theo danh mục, bạn muốn hiển thị Tên danh mục (được lưu trong bảng Products). Thay vì kéo vào Pivot, bạn có thể:

1. Thêm cột “Tên danh mục” trong bảng Sales bằng công thức:
   =VLOOKUP(@MãSP, Products!$A$2:$C$100, 3, FALSE)
2. Sau đó làm mới PivotTable – cột mới sẽ xuất hiện và bạn có thể dùng để phân nhóm.

5. Tổng kết và lời khuyên thực tiễn

1. Luôn dùng FALSE cho range_lookup trừ khi dữ liệu đã được sắp xếp và bạn muốn tìm gần đúng.
2. Kiểm tra định dạng (số vs. văn bản, ngày) trước khi áp dụng VLOOKUP/HLOOKUP.
3. Sử dụng IFERROR để làm cho bảng tính thân thiện hơn với người dùng cuối.
4. Khi dữ liệu lớn (>10k dòng), hãy chuyển sang Table, Named Range hoặc XLOOKUP để tối ưu tốc độ.
5. Đối với tra cứu đa tiêu chí, ghép các trường thành một “khóa” duy nhất (concatenation) hoặc chuyển sang INDEX-MATCH/XLOOKUP.
6. Luôn giữ bản sao dữ liệu gốc để tránh thay đổi vô tình khi mở rộng bảng.

6. Tài liệu tham khảo và nguồn học thêm

Kết luận

VLOOKUP và HLOOKUP là nền tảng của việc tra cứu dữ liệu trong Excel. Khi thành thạo, bạn có thể giải quyết hầu hết các yêu cầu liên quan đến tìm kiếm thông tin, tổng hợp báo cáo và tự động hoá quy trình làm việc. Bài viết đã cung cấp từ cú pháp cơ bản, lưu ý quan trọng, bộ đề thực hành đến các kỹ thuật nâng cao và công cụ thay thế. Hãy thực hành các bài tập trên, áp dụng vào công việc thực tế và dần dần nâng cao kỹ năng của mình. Chúc bạn thành công!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.