Có thể bạn quan tâm: Bài Tập Excel Hàm Tài Chính Có Lời Giải
Giới thiệu
VLOOKUP (Vertical Lookup) là một trong những hàm Excel được sử dụng rộng rãi nhất, đặc biệt trong việc tra cứu dữ liệu giữa các bảng tính. Khi làm việc với dữ liệu lớn, khả năng tìm kiếm nhanh chóng và chính xác thông tin là yếu tố quyết định hiệu suất công việc. Để giúp các học viên, sinh viên và các chuyên gia nghiệp vụ thực hành, bài viết này sẽ cung cấp một loạt các bài tập thực tế về hàm VLOOKUP, kèm theo lời giải chi tiết. Mỗi bài tập sẽ dần dần nâng cao độ khó, giúp người đọc nắm vững cách áp dụng hàm VLOOKUP trong các tình huống khác nhau.
Có thể bạn quan tâm: Bài Tập Excel Cty Tnhh Đại Thái Bình Dương
Nêu mục tiêu học tập
- Hiểu rõ cú pháp và các tham số của hàm VLOOKUP: lookup_value, table_array, col_index_num, range_lookup.
- Áp dụng VLOOKUP trong các tình huống thực tế: tra cứu giá sản phẩm, দাঁ người dùng, số điện thoại, dữ liệu nhân viên, …
- Khắc phục các lỗi thường gặp: #N/A, #REF!, không tìm được giá trị đúng.
- Kết hợp VLOOKUP với các hàm khác: IFERROR, INDEX/MATCH, CONCATENATE, TEXT, LEFT, Maxamed.
- Tối ưu hoá công thức: tránh lặp lại công thức, sử dụng tham chiếu cố định, tên vùng dữ liệu.
Có thể bạn quan tâm: Bài Tập Excel Bảng Theo Dõi Nhập Xuất Hàng
Cách sử напрям VLOOKUP
Cú pháp cơ bản
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
- lookup_value: Giá trị cần tra cứu.
- table_array: Mảng dữ liệu chứa giá trị cần tra cứu và kết quả trả về. Cột đầu tiên của mảng phải chứa lookup_value.
- col_index_num: Số thứ tự cột trong table_array chứa dữ liệu trả về (bắt đầu từ 1).
- range_lookup: Tùy chọn, TRUE (hoặc bỏ trống) cho phép tìm kiếm gần nhất, FALSE cho phép tìm kiếm chính xác.
Ví dụ đơn giản
Giả sử một bảng dữ liệu sản phẩm:
| A | B | C |
|---|---|---|
| M Tik | Mã sản phẩm | Giá |
| 001 | TShirt | 200 |
| 002 | Quần | 400 |
| 003 | Giày | 600 |
Để tra cứu giá của sản phẩm với mã 002, công thức:
=VLOOKUP("002", $A$2:$C$4, 3, FALSE)
Kết quả trả về là 400.
Bài tập thực hành
Bài tập 1: Tra cứu giá thành sản phẩm
Mô tả
Bạn có một bảng dữ liệu Sản phẩm như sau:
| A | B | C |
|---|---|---|
| Mã sản phẩm | Tên_NONE | Giá |
| 001 | TShirt | 200 |
| 002 | Quần | 400 |
| 003 | Giày | 600 |
| 004 | Áo khoác | 800 |
Bạn có một bảng Đơn hàng:
| A | B |
|---|---|
| Mã sản phẩm | Số lượng |
| 001 | 5 |
| 003 | 2 |
| 004 | 1 |
| 002 | 3 |
Yêu cầu: Thêm cột Giá trong bảng Đơn hàng. Sử dụng VLOOKUP để lấy giá từ bảng Sản phẩm. Thêm cột Tổng tiền = Số lượng Giá.
Lời giải
- Thêm cột C (Giá) vào bảng Đơn hàng, đặt công thức tại C2:
=VLOOKUP(A2, $A$2:$C$5, 3, FALSE) A2là mã sản phẩm trong Đơn hàng.$A$2:$C$5là vùng dữ liệu Sản phẩm, được cố định bằng dấu$để tránh thay đổi khi sao chép công thức.3là cột Giá trong vùng dữ liệu Sản phẩm.FALSEđể tìm kiếm chính xác.Kéo công thức xuống các ô C3:C5.
Thêm cột D (Tổng tiền) tại D2:
=B2C2
Kéo công thức xuống D3:D5.
Bài tập 2: Tra cứu dữ liệu nhân viên
Mô tả
Bạn có bảng Nhân viên:
| A | B | C |
|---|---|---|
| Mã NV | Tên | Phòng ban |
| 001 | Nguyễn Văn A | Kế toán |
| 002 | Trần Thị B | Kinh doanh |
| 003 | Lê Văn C | IT |
Bảng Báo cáo công việc:
| A | B | C |
|---|---|---|
| Mã NV | Thời gian làm việc | Kỹ năng |
| 001 | 8 giờ | Excel |
| 002 | 7 giờ | Bán hàng |
| 003 | 9 giờ | Lập trình |
| 001 | 5 giờ | Báo cáo |
Yêu cầu: Thêm cột Tên và Phòng ban vào bảng Báo cáo công việc. Sử dụng VLOOKUP để tra cứu từ bảng Nhân viên.
Lời giải
- Thêm cột D (Tên) tại D2:
=VLOOKUP(A2, $A$2:$C$4, 2, FALSE) 2là cột Tên trong bảng Nhân viên.Thêm cột E (Phòng ban) tại E2:
=VLOOKUP(A2, $A$2:$C$4, 3, FALSE)Kéo công thức xuống các ô tương ứng.
Bài tập 3: Tra cứu dữ liệu khách hàng
Mô tả
Bảng Khách hàng:
| A | B | C |
|---|---|---|
| Mã KH | Tên khách hàng | Số điện thoại |
| K001 | Phạm Thị A | 0987654321 |
| K002 | Đặng Văn B | 0912345678 |
| K003 | Lê Thị C | 0976543210 |
Bảng Giao dịch:
| A | Beton | objek |
|---|---|---|
| Mã strcat | Mục đích | Số tiền |
| K001 | Mua hàng | 500000 |
| K002 | Thanh toán | 200000 |
| K003 | Mua hàng | 300000 |
| K002 | Thanh toán | 150000ność |
Yêu cầu: Thêm cột Tên khách hàng và Số điện thoại vào bảng Giao dịch.
Lời giải
Cột D (Tên khách hàng) tại D2:
=VLOOKUP(A2, $A$2:$C$4, 2, FALSE)Cột E (Số điện thoại) tại E2:
=VLOOKUP(A2, $A$2:$C$4, 3, FALSE)Kéo công thức xuống.
Bài tập 4: Xử lý lỗi khi không tìm được giá trị
Mô tả
Trong bảng Đơn hàng có một mã sản phẩm 005 không tồn tại trong bảng Sản phẩm. Khi áp dụng công thức VLOOKUP, Excel sẽ trả về lỗi #N/A.
Yêu cầu: Sửa 느 công thức để khi không tìm thấy giá trị, hiển thị một thông báo “Không tìm thấy” thay vì lỗi.
Lời giải
Sử dụng hàm IFERROR:
=IFERROR(VLOOKUP(A2, $A$2:$C$5, 3, FALSE), "Không tìm thấy")
Kéo công thức xuống.
Bài tập 5: Tra cứu nhiều giá trị (VLOOKUP đa chiều)
Mô tả
Bảng Doanh thu:
| A | B | C | D |
|---|---|---|---|
| Tháng | Kế toán | IT | Kinh doanh |
| 1 | 500000 | 200000 | 300000 |
| 2 | 600000 | 250000 | 350000 |
| 3 | 550000 | 220000 | 320000 |
Bạn muốn tra cứu doanh thu của một phòng ban cụ thể trong một tháng. Ví dụ: Doanh thu của IT trong tháng 2.
Yêu cầu: Sử dụng VLOOKUP kết hợp với INDEX/MATCH để tra cứu dữ liệu theo hai tiêu chí: Tháng và Phòng ban.
Lời giải
=INDEX($B$2:$D$4, MATCH(B1, $A$2:$A$4, 0), MATCH(C1, $B$1:$D$1, 0))
Trong đó:
– B1 chứa tháng cần tra cứu (2).
– C1 chứa phòng ban cần tra cứu (IT).
– MATCH(B1, $A$2:$A$4, 0) trả về hàng phù hợp.
– MATCH(C1, $B$1:$D$1, 0) trả về cột phù hợp.
– INDEX lấy giá trị tại vị trí giao nhau.
Bài tập 6: Tra cứu dữ liệu chính xác khi có nhiều giá trị trùng nhau
Mô tả
Bảng Học sinh:
| A | B | C |
|---|---|---|
| Mã HS | Tên | Lớp |
| 001 | Nguyễn A | 10A |
| 002 | Trần B | 10A |
| 003 | Lê C | 10B |
| 001 | Nguyễn A | 10B |
Bạn muốn tra cứu Lớp của học sinh có mã 001 và tên Nguyễn A. Do có hai bản ghi, VLOOKUP sẽ trả về giá trị đầu tiên. Cần xác định đúng bản ghi theo cả hai tiêu chí.
Lời giải
Sử dụng công thức mảngFiled:
=INDEX($C$2:$C$5, MATCH(1, ($A$2:$A$5="001")( $B$2:$B$5="Nguyễn A"), 0))
Lưu ý: Để công thức hoạt động, bạn cần nhập bằng Ctrl+Shift+Enter (trong Excel cũ) hoặc sử dụng hàm LAMBDA/LET trong Excel mới.
Bài tập 7: Tra cứu dữ liệu động với tên vùng
Mô tả
introduced một bảng Dự án:
| A | B | C |
|---|---|---|
| Mã dự án | Mô tả | Ngày bắt đầu |
| D001 | Xây dựng nhà | 01/01/2026 |
| D002 | Tối ưu hệ thống | 15/02/2026 |
| D003 | Nghiên cứu thị trường | 01/03/2026 |
Bạn có một bảng Tình trạng dự án:
| A | B |
|---|---|
| Mã dự án | Tình trạng |
| D البحر | Đang tiến hành |
| D002 | Đã hoàn thành |
| D003 | Đang tiến hành |
Yêu cầu: Sử dụng VLOOKUP với tên vùng Dự án thay vì tham chiếu ô cố định.
Lời giải
Trước hết, đặt tên vùng cho bảng Dự án:
- Chọn vùng A2:C4, rồi trong thanh tên (trên trái ô A1) nhập
DuAn.
Sau đó, công thức VLOOKUP trong bảng Tình trạng dự án:
=VLOOKUP(A2, DuAn, 3, FALSE)
Kết quả trả về là ngày bắt đầu của dự án tương ứng.
Bài tập 8: Kombin VLOOKUP và IF để lọc dữ liệu
Mô tả
Bảng Bán hàng:
| A | B | C |
|---|---|---|
| Khách hàng | Sản phẩm | Số lượng |
| A001 | 001 | 5 |
| A001 | 003 | 2 |
| A002 | 002 | 3 |
| A003 | 001 | 1 |
Bạn muốn tính tổng số lượng bán ra cho khách hàng A001 với sản phẩm 001. Nếu tổng số lượng nhỏ hơn 4, hiển thị “Thấp”, nếu lớn hơn hoặc bằng 4, hiển thị “gam”.
Lời giải
Sử dụng công thức mảng:
=IF(SUMIFS(C2:C5, A2:A5, "A001", B2:B5, "001") >= 4, "Có", "Thấp")
SUMIFStính tổng số lượng khi cả hai điều kiện thỏa mãn.IFkiểm tra giá trị.
Bài tập 9: Tra cứu dữ liệu trong nhiều bảng
Mô tả
Bạn có hai bảng Nhân viên và Bảng lương. Mỗi bảng chứa một cột Mã NV. Bạn muốn tra cứu lương của mỗi nhân viên trong bảng Nhân viên và hiển thị trong cột Lương.
Lời giải
Bảng
Nhân viên:
| A | B | C |
|——-|—————-|——-|
| Mã NV | Tên | Lương |
| 001 | Nguyễn A | |
| 002 | Trần B | |
| 003 | Lê C | |
Bài Tập Excel Về Hàm Vlookup Có Lời Giải Có thể bạn quan tâm: Bài Tập Excel: Hàm Vlookup Kết Hợp Hàm If
Bảng
Bảng lương:
| A | B |
|——-|——-|
| Mã NV | Lương |
| 001 | 500000|
| 002 | 600000|
| 003 Portland | 550000|
Công thức VLOOKUP ở cột C (Nhân viên):
=VLOOKUP(A2, $A$2:$B$4, 2, FALSE)
Kéo công thức xuống.
Bài tập 10: Kiểm tra tính hợp lệ của dữ liệu
Mô tả
Bạn muốn kiểm tra xem mã sản phẩm trong bảng Đơn hàng có tồn tại trong bảng Sản phẩm hay không. Nếu không tồn tại, hiển thị thông báo “Mã không hợp lệ”.
Lời giải
Sử dụng IF và ISNA:
=IF(ISNA(VLOOKUP(A2, $A$2:$C$5, 1, FALSE)), "Mã không hợp lệ", "Hợp lệ")
Bài tập 11: Tra cứu dữ liệu với định dạng ngày
Mô tả
Bảng Đặt phòng rt:
| A | B | C |
|---|---|---|
| Phòng | Ngày check-in | Ngày check-out |
| 101 | 01/01/2026 | 05/01/2026 |
| 102 | 02/01/2026 | 06/01/2026 |
| 103 | 03/01/2026 | 07/01/2026 |
Bạn muốn tra cứu số ngày lưu trú của phòng 101.
Lời giải
Sử dụng công thức:
=VLOOKUP(101, $A$2:$C$4, 3, FALSE)-VLOOKUP(101, $A$2:$C$4, 2, FALSE)
Kết quả trả về là số ngày lưu trú (4).
Bài tập 12: Tra cứu dữ liệu phức tạp với nhiều điều kiện
Mô tả
Bảng Kết quả học tập:
| A | B | C | D |
|---|---|---|---|
| Mã HS | Ngành | Điểm | Đạt? |
| 001 | Toán | 85 | |
| 001 | Văn | 90 | |
| 002 | Toán | 70 | |
| 002 | Văn | 75 | |
| 003 | Toán | 95 | |
| 003 | Văn | 80 |
Bạn muốn tra cứu điểm trung bình của một học sinh trong một ngành. Ví dụ: điểm trung bình của học sinh 001 trong ngành Toán.
Lời giải
Sử dụng công thức mảng:
=AVERAGEIFS(C$2:C$7, A$2:A$7, "001", B$2:B$7, "Toán")
Bài tập 13: Tra cứu dữ liệu với điều kiện “Bắt đầu với”
Mô tả
Bảng Khách hàng:
| A | B |
|---|---|
| Mã KH | Tên khách hàng |
| 001 | Nguyễn A |
| 002 | Trần B |
| 003 | Lê C |
| 004 zadovolj | Đặng D |
Bạn muốn tra cứu mã khách hàng khi nhập tên khách hàng, nhưng trong trường hợp có nhiều khách hàng có tên bắt đầu bằng cùng một chuỗi, bạn muốn lấy mã khách hàng đầu tiên.
Lời giải
Sử dụng công thức mảng conjunction:
=INDEX(A$2:A$5, MATCH(TRUE, LEFT(B$2:B$5, LEN("Nguyễn"))="Nguyễn", 0))
Bài tập 14: Tra cứu dữ indique
Mô tả
Bảng Chi phí sản xuất:
| A | B | C |
|---|---|---|
| Mã SP | Ngày | Chi phí |
| 001 | 01/01/ | 2000 |
| 001 | 02/01/ | 2500によ |
| 002 | 01/01/ | 1800 |
| 002 | 02/01/ | 1900 |
Bạn muốn tra cứu chi phí của một sản phẩm trong một ngày cụ thể.
Lời giải
Sử dụng công thức mảng:
=INDEX(C$2:C$5, MATCH(1, (A$2:A$5="001")(B$2:B$5=DATE(2026,1,2)), 0))
Bài tập 15: Tra cứu dữ liệu với điều kiện “Kết thúc bằng”
Mô tả
Bảng Hóa đơn:
| A | B |
|---|---|
| Mã HD | Mã sản phẩm |
| HD001 | 001 |
| HD002 | 002 |
| HD003 | 003 |
| HD004 | 001 |
Bạn muốn tra cứu mã sản phẩm khi nhập mã hóa đơn, nhưng nếu có nhiều mã sản phẩm, lấy mã sản phẩm cuối cùng.
Lời giải
Sử dụng công thức mảng:
=INDEX(B$2:B$5, MATCH(1, (LEFT(A$2:A$5, 5)=LEFT("HD004",5)), 0))
Bài tập 16: Tra cứu dữ liệu với điều kiện “Trong khoảng”
Mô tả
Bảng Doanh thu hàng ngày:
| A | B | C |
|---|---|---|
| Ngày | Kênh | Doanh thu |
| 01/01/2026 | Online | 1000 |
| 02/01/2026 | Offline | 800 |
| 03/01/2026 | Online | 1200 |
| 04/01/2026 | Offline | 950 |
Bạn muốn tra cứu tổng doanh thu của một kênh trong một khoảng thời gian nhất định.
Lời giải
Sử dụng công thức ❤:
=SUMIFS(C$2:C$5, B$2:B$5, "Online", A$2:A$5, ">="&DATE(2026,1,1), A$2:A$5, "<="&DATE(2026,1,3))
Bài tập 17: Tra cứu dữ liệu với điều kiện “Mật khẩu”
Mô tả
Bảng Đăng nhập:
| A | B | C |
|---|---|---|
| Tài khoản | Mật khẩu | ID |
| user1 | pass1 | 001 |
| user2 | pass2 | 002 |
| user3 | pass3 | 003 |
Bạn muốn tra cứu ID khi nhập tài khoản và mật khẩu, nhưng trong trường hợp nhiều tài khoản có mật khẩu giống nhauitoria.
Lời giải
Sử dụng công thức mảng:
=INDEX(C$2:C$4, MATCH(1, (A$2:A$4="user2")(B$2:B$4="pass2"), 0))
Tổng kết
1乘 1
VLOOKUP là một công cụ mạnh mẽ giúp tự động hóa quá trình tra cứu dữ liệu trong Excel. Bằng cách áp dụng các bài tập từ cơ bản đến nâng cao, người học có thể thực sự nắm vững cách thức hoạt động và khắc phục các lỗi thường gặp.
2 thực hành
Đừng chỉ đọc công thức, hãy thao tác thực tế trên bảng tính. Khi áp dụng công thức trong thực tế, bạn sẽ nhận ra những điểm mấu chốt: cách đặt tham chiếu cố định, cách xử lý lỗi, cách kết hợp với các hàm khác.
3. Kỹ năng nâng cao
Khi đã thành thạo VLOOKUP, bạn có thể chuyển sang INDEX/MATCH để tránh hạn chế của VLOOKUP (ví dụ: chỉ tìm kiếm cột từ trái sang phải). Đồng thời, bạn cũng nên tìm hiểu về XLOOKUP – một hàm mới trong Excel 365, linh hoạt hơn và hỗ trợ cả tìm kiếm hai chiều.
4. Ứng dụng thực tế
Các bài tập ở trên đại diện cho các tình huống thực tế: quản lý kho, bán hàng, nhân sự, partículas, dịch vụ khách hàng. Khi áp dụng vào công việc hàng ngày, VLOOKUP sẽ giúp tiết kiệm thời gian, giảm sai sót và nâng cao hiệu suất.
5. Bước tiếp theo
- Thử áp dụng VLOOKUP trong một dự án thực tế của bạn: ví dụ, tạo bảng báo cáo doanh thu, bảng tồn kho, bảng phân loại khách hàng.
- Khám phá XLOOKUP và FILTER để thay thế VLOOKUP trong các phiên bản Excel mới hơn.
- Học cách sử dụng Pivot Tables kết hợp với VLOOKUP để tạo báo cáo tổng hợp.
Kு
Bài tập và lời giải trên đây là một công cụ học tập hữu ích, nhưng hãy nhớ rằng thực hành liên tục là chìa khóa để trở thành chuyên gia Excel. Mỗi khi bạn gặp một tình huống mới, hãy thử áp dụng VLOOKUP (hoặc các hàm khác) để tìm ra giải pháp tối ưu. Chúc bạn học tập hiệu quả và thành công trong mọi dự án!
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ẻ.