Có thể bạn quan tâm: File Excel Quản Lý Thu Chi Công Trình Xây Dựng: Công Cụ Đắc Định Cho Sự Thành Công
Giới thiệu chung
Trong công việc quản lý dữ liệu, báo cáo, hay phân tích số liệu, Excel luôn là công cụ không thể thiếu. Một trong những hàm được sử dụng rộng rãi nhất là hàm VLOOKUP (tạm dịch: Tìm kiếm theo cột dọc). Hàm này cho phép chúng ta tra cứu giá trị trong một bảng dữ liệu dựa trên một giá trị duy nhất trong cột đầu tiên. Tuy nhiên, trong thực tiễn, chúng ta thường gặp phải tình huống cần tìm kiếm dựa trên nhiều điều kiện đồng thời – ví dụ: tìm giá trị bán hàng của một sản phẩm cụ thể trong một cửa hàng cụ thể, hoặc lấy điểm số của một sinh viên trong một môn học nhất định.
VLOOKUP vốn chỉ hỗ trợ một tiêu chí duy nhất, vì vậy khi cần sử dụng nhiều tiêu chí, người dùng thường phải tìm cách “bổ sung” hoặc “điều chỉnh” hàm. Trong bài viết này, chúng ta sẽ đi sâu vào các phương pháp phổ biến, kèm theo các ví dụ thực tế, để bạn có thể áp dụng ngay vào công việc của mình.
Có thể bạn quan tâm: Chuyển Tọa Độ Từ Google Earth Sang Vn2000 Trong Excel
1. VLOOKUP cơ bản – Nhắc lại cách sử dụng
Trước khi đi vào các phương pháp nâng cao, hãy nhanh chóng ôn lại cú pháp của VLOOKUP:
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
- lookup_value: Giá trị mà bạn muốn tra cứu.
- table_array: Phạm vi bảng dữ liệu cần tìm kiếm.
- col_index_num: Số thứ tự cột trong bảng dữ liệu mà bạn muốn lấy giá trị trả về.
- range_lookup: (tuỳ chọn) TRUE (hoặc bỏ trống) – tìm kiếm gần nhất; FALSE – tìm kiếm chính xác.
Ví dụ đơn giản: Bạn có bảng dữ liệu A1:C10 chứa mã sản phẩm, tên sản phẩm, và giá. Bạn muốn tìm orientation theo mã sản phẩm P001:
=VLOOKUP("P001", A1:C10, 3, FALSE)
Kết quả trả về là giá của P001.
Có thể bạn quan tâm: Download Phần Mềm Kế Toán Excel Mới Nhất Miễn Phí – Hướng Dẫn Chi Tiết Và Đầy Đủ Thông Tin
2. Vấn đề khi cần nhiều điều kiện
Hàm VLOOKUP chỉ nhận một lookup_value. Khi bạn muốn tra cứu dựa trên hai hoặc bốn điều kiện, bạn có thể gặp các rào cản sau:
- Không thể nhập nhiều giá trị tìm kiếm vào một tham số duy nhất.
- Không thể áp dụng trực tiếp các điều kiện phụ (như so sánh >, <, <>).
- Xử lý dữ liệu lớn: Khi dữ liệu lớn, phương pháp nặng nề (như hợp chuỗi) có thể làm giảm tốc độ tính toán.
Vì vậy, chúng ta cần tìm các giải pháp “đánh bại” vấn đề này. Dưới đây là các phương pháp phổ biến:
3. Phương pháp 1: Sử dụng cột trợ giúp (Helper Column)
3.1. Ý tưởng
Tạo một cột mới trong bảng dữ liệu, kết hợp (concatenate) tất cả các điều kiện thành một chuỗi duy nhất. Sau đó, sử dụng VLOOKUP để tra cứu theo chuỗi này.
3.2. Cách thực hiện
Giả sử bạn có bảng dữ liệu Sheet1:
| A (Mã sản phẩm) | B (Tên cửa hàng) | C (Ngày bán) | D (Số lượng) |
|---|---|---|---|
| P001 | Cửa hàng A | 2026-01-01 | 50 |
| P002 | Cửa hàng B | 2026-01-02 | 30 |
| P001 | Cửa hàng B | 2026-01-03 | 20 |
| … | … LIST… | … | … |
Bước 1: Thêm cột E (đặt tên “Key”) với công thức:
=CONCATENATE(A2, "|", B2, "|", TEXT(C2,"yyyy-mm-dd"))
Hoặc dùng hàm TEXTJOIN:
=TEXTJOIN("|", TRUE, A2, B2, TEXT(C2,"yyyy-mm-dd"))
Bước 2: Tạo bảng tra cứu mới hoặc sử dụng dữ liệu đã có, nhưng cần có cột “Key” tương ứng.
Bước 3: Sử dụng VLOOKUP:
=VLOOKUP(CONCATENATE("P001", "|", "Cửa hàng B", "|", TEXT("2026-01-03", "yyyy-mm-dd")), Sheet1!$E$2:$D$100, 2, FALSE)
Ở đây Sheet1!$E$2:$D$100 là phạm vi bảng bao gồm cột “Key” và cột cần lấy (cột D – Số lượng). col_index_num là 2 vì cột “Số lượng” là cột thứ hai trong phạm vi này.
3.3. Ưu điểm và hạn chế
- Ưu điểm: Đơn giản, dễ hiểu, không cần công thức phức tạp.
- Hạn chế: Khi dữ liệu thay đổi, cột “Key” cần được cập nhật; nếu có nhiều điều kiện, chuỗi concat có thể dài và gây nhầm lẫn.
4. Phương pháp 2: Sử dụng INDEX + MATCH với mảng
4.1. Ý tưởng
Hàm INDEX trả về giá trị tại một vị trí, trong khi MATCH tìm vị trí của một giá trị trong một dãy. Khi kết hợp với mảng (array) hoặc hàm IF, chúng ta có thể tìm kiếm theo nhiều điều kiện mà không cần cột trợ giúp.
4.2. Công thức mẫu
Giả sử bạn muốn lấy số lượng bán của P001 tại Cửa hàng B vào ngày 2026-01-03. Công thức:
=INDEX(Sheet1!$D$Sheets!$D$2:$D$100, MATCH(1, (Sheet1!$A$2:$A$100="P001") (Sheet1!$B$2:$B$100="Cửa hàng B") (Sheet1!$C$2:$C$100=DATE(2026,1,3)), 0))
Lưu ý: Đây là công thức mảng, nên cần nhấn Ctrl+Shift+Enter (trong Excel 2026 hoặc cũ). Trong Excel 365, công thức này sẽ tính tự động.
4.3. Giải thích
INDEX(Sheet1!$D$2:$D$100, ...)trả về giá trị trong cột D (số lượng).MATCH(1, ..., 0)tìm vị trí đầu tiên mà biểu thức điều kiện trả về TRUE (1). Mỗi điều kiện được nhân () với nhau: nếu tất cả đều TRUE, kết quả là 1; nếu bất kỳ điều kiện nào FALSE, kết quả là 0.
4.4. Ưu điểm và hạn chế
- Ưu điểm: Không cần cột trợ giúp, linh hoạt với nhiều điều kiện.
- Hạn chế: Công thức dài, dễ gây nhầm lẫn; khi có nhiều điều kiện, tính toán có thể chậm đối với bảng dữ liệu lớn.
5. Phương pháp 3: SUMPRODUCT – Tìm kiếm với nhiều điều kiện
Có thể bạn quan tâm: Cách Làm Cho Ô Excel Tự Động Mở Rộng Để Vừa Văn Bản: Hướng Dẫn Chi Tiết & Mẹo Thực Tiễn
5.1. Ý tưởng
Hàm SUMPRODUCT có thể thực hiện phép nhân và tổng mảng, cho phép chúng ta tìm kiếm giá trị dựa trên nhiều điều kiện. Khi chỉ có một kết quả duy nhất, SUMPRODUCT trả về giá trị đó.
5.2. Công thức mẫu
=SUMPRODUCT((Sheet1!$A$2:$A$100="P001") (Sheet1!$B$2:$B$100="Cửa hàng B") (Sheet1!$C$2:$C$100=DATE(2026,1,3)) Sheet1!$D$2:$D$100)
Giải thích:
- Mỗi cột điều kiện được chuyển thành mảng 0/1 (TRUE/FALSE).
- Khi nhân ba mảng điều kiện với nhau, chỉ các hàng thỏa mãn cả ba điều kiệnSorter có giá trị 1.
- Cuối cùng nhân với cột D (số lượng) và tính tổng. Nếu có duy nhất một hàng thỏa mãn, tổng sẽ là giá trị mong muốn.
5.3. Ưu điểm và hạn chế
- Ưu điểm: Không cần cột trợ giúp, không phải công thức mảng đặc biệt, dễ đọc hơn so với INDEX/MATCH.
- Hạn chế: Khi có nhiều hàng thỏa mãn (tên sản phẩm, cửa hàng, ngày phân loại không duy nhất), kết quả sẽ là tổng của chúng, không phải giá trị duy nhất.
6. Phương pháp 4: Sử dụng hàm FILTER (Excel 365 và 2026)
6.1. Ý tưởng
Trong phiên bản mới của Excel, hàm FILTER cho phép trả về một dải giá trị thỏa mãn một hoặc nhiều điều kiện. Khi kết hợp với INDEX hoặc XLOOKUP, ta có thể lấy giá trị duy nhất.
6.2. Công thức mẫu
=INDEX(FILTER(Sheet1!$D$2:$D$100, (Sheet1!$A$2:$A$100="P001") (Sheet1!$B$2:$B$100="Cửa hàng B") (Sheet1!$C$2:$C$100=DATE(2026,1,3)), 1)
Hoặc nếu bạn muốn lấy toàn bộ giá trị thỏa mãn:
=FILTER(Sheet1!$D$2:$D$100, (Sheet1!$A$2:$A$100="P001") (Sheet1!$B$2:$B$100="Cửa hàng B") (Sheet1!$C$2:$C$100=DATE(2026,1,3)))
6.3. Ưu điểm và hạn chế
- Ưu điểm: Đơn giản, linh hoạt, hỗ trợ trả về nhiều kết quả.
- Hạn chế: Chỉ có trong Excel 365/2026; không hỗ trợ trong phiên bản cũ.
7. Phương pháp 5: XLOOKUP – Hàm mới thay thế VLOOKUP
7.1. Ý tưởng
Hàm XLOOKUP mới trong Excel cung cấp tính linh hoạt hơn VLOOKUP熟, bao gồm khả năng tìm kiếm theo nhiều điều kiện. Tuy nhiên, XLOOKUP vẫn chỉ nhận một lookup_value. Để áp dụng nhiều điều kiện, ta vẫn cần một “key” hoặc dùng công thức mảng.
7.2. Công thức mẫu (dùng helper column)
= XLOOKUP(CONCATENATE("P001","|","Cửa hàng B","|",TEXT("2026-01-03","yyyy-mm-dd")), Sheet1!$E$2:$E$100, Sheet1!$D$2:$D$100, "Không tìm thấy")
7.3. Ưu điểm và hạn chế
- Ưuη điểm: Hỗ trợ tìm kiếm chính xác, không yêu cầu cột tìm kiếm phải là cột đầu tiên trong bảng.
- Hạn chế: Khi dùng nhiều điều kiện, vẫn cần helper column hoặc công thức phức tạp.
8. Ví dụ thực tiễn – Phân tích dữ liệu bán hàng
8.1. Bảng dữ liệu mẫu
| Mã sản phẩm | Tên sản phẩm | Cửa hàng | Ngày bán | Giá bán | Số lượng |
|---|---|---|---|---|---|
| P001 | Áo khoác | A | 2026-01-01 | 500000 | 10 |
| P002 | Quần jean | B אָבער | 2026-01-02 | 300000 | 5 |
| P001 | Áo khoác | B | 2026-01-03 | 500000 | 8 |
| P003 | Giày thể thao | A | 2026-01-04 | 700000 | 3 |
| … | … | … | … | … | … |
8.2. Yêu cầu
- Tính tổng doanh thu (Giá bán × Số lượng) của
Áo khoáctạiCửa hàng Btrong tháng 01/2026. - Tìm số lượng bán của
Giày thể thaotạiCửa hàng Avào ngày2026-01-04.
8.3. Giải pháp
8.3.1. Tính tổng doanh thu
Sử dụng SUMPRODUCT:
=SUMPRODUCT((Sheet1!$B$2:$B$100="Áo khoác") (Sheet1!$C$2:$C$100="B") (TEXT(Sheet1!$D$2:$D$100,"yyyy-mm")="2026-01") Sheet1!$E$2:$E$100 Sheet1!$F$2:$F$100)
8.3.2. Lấy số lượng bán
Sử dụng INDEX + MATCH:
=INDEX(Sheet1!$F$2:$F$100, MATCH(1, (Sheet1!$B$2:$B$100="Giày thể thao") (Sheet1!$C$2:$C$100="A") (Sheet1!$D$2:$D$100=DATE(2026,1,4)), 0))
9. Lưu ý khi áp dụng
| Kiến thức | Mô tả | Lời khuyên |
|---|---|---|
| Cột trợ giúp | Đơn giản nhưng cần cập nhật khi dữ liệu thay đổi | Kiểm tra कोविड dữ liệu thường xuyên. |
| Hàm mảng | Đòi hỏi nhập Ctrl+Shift+Enter (cũ) | Sử dụng tính năng “Dynamic Arrays” trong Excel 365 để tránh thao tác phức tạp. |
| SUMPRODUCT | Dễ đọc nhưng không phù hợp với nhiều kết quả | Kiểm tra xem có nhiều hàng thỏa mãn không; nếu có, sẽ phải lọc thêm. |
| FILTER | Hỗ trợ trả về nhiều kết quả | Phù hợp cho các báo cáo động, nhưng yêu cầu Excel 365/2026. |
| Performance | Các công thức phức tạp có thể làm chậm tính toán | Giảm kích thước phạm vi dữ liệu; sử dụng công thức tính động thay vì công thức cố định. |
| Kiểm tra lỗi | Hàm VLOOKUP/MATCH có thể trả về lỗi #N/A | Sử dụng IFERROR để hiển thị thông báo thân thiện. |
10. Kết luận
Trong thế giới dữ liệu ngày nay, khả năng tra cứu và phân tích dữ liệu theo nhiều tiêu chí đồng thời là một kỹ năng cốt lõi. Excel, với các hàm như VLOOKUP, INDEX/MATCH, SUMPRODUCT, FILTER hoặc XLOOKUP, cung cấp đủ công cụ để giải quyết vấn đề này – dù bạn đang làm việc với dữ liệu bán hàng, tình hình tài chính, hay bất kỳ bảng dữ liệu nào khác.
- Nếu bạn cần một giải pháp nhanh, dễ hiểu, helper column là lựa chọn tốt.
- Khi cần linh hoạt và không muốn phụ thuộc vào cột phụ, hãy dùng INDEX + MATCH hoặc SUMPRODUCT.
- Đối với Excel 365/2026, FILTER sẽ mang lại trải nghiệm tối ưu và dễ dàng mở rộng.
- Đừng quên kiểm tra hiệu suất và tối ưu phạm vi dữ liệu để tránh những trục trặc khi làm việc với bảng dữ liệu lớn.
Hy vọng qua bài viết này, bạn đã có thêm nhiều công cụ và ý tưởng để áp dụng trong công việc hàng ngày của mình. Hãy thử nghiệm từng phương pháp, so sánh hiệu suất và chọn lựa giải pháp phù hợp nhất với quy trình làm việc của bạn. Chúc bạn thành công trong việc khai thác dữ liệu và đưa ra những quyết định chính xác hơ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ẻ.