Hàm lấy dữ liệu theo điều kiện trong Excel là các hàm như SUMIF, COUNTIF, INDEX, MATCH, VLOOKUP, XLOOKUP, FILTER... giúp tự động lọc và trích xuất dữ liệu theo tiêu chí cụ thể - không cần thao tác thủ công từng dòng.

Việc xử lý và phân tích dữ liệu trong Excel trở nên phức tạp khi bạn cần trích xuất thông tin theo tiêu chí nhất định. Thay vì lọc thủ công từng dòng, các hàm lookup Excel và hàm lọc dữ liệu theo điều kiện giúp bạn tự động hóa quy trình một cách nhanh chóng và chính xác. Nếu bạn đang tìm hiểu thủ thuật Excel từ A-Z, đây là một trong những kỹ năng quan trọng nhất cần nắm vững.

Bài viết của TechCare.vn phân tích đầy đủ các hàm Excel nâng cao quan trọng nhất, kèm ví dụ thực tế và mẹo tránh lỗi phổ biến.

Tổng Quan Các Hàm Lấy Dữ Liệu Có Điều Kiện

Nhóm hàm tính toán có điều kiện

SUMIF và SUMIFS là hai hàm được dùng phổ biến nhất khi cần tính tổng các giá trị thỏa mãn điều kiện. SUMIF áp dụng cho một điều kiện duy nhất, trong khi SUMIFS cho phép thiết lập nhiều điều kiện đồng thời.

COUNTIF và COUNTIFS hoạt động tương tự nhưng thay vì tính tổng, chúng đếm số lượng ô dữ liệu đáp ứng tiêu chí - rất hữu ích khi bạn cần biết có bao nhiêu giao dịch vượt một mức giá trị, hoặc số nhân viên đạt chỉ tiêu.

AVERAGEIF và AVERAGEIFS tính giá trị trung bình của các ô thỏa mãn điều kiện, giúp phân tích xu hướng trung tâm của tập dữ liệu được lọc.

Nhóm hàm tra cứu và trích xuất dữ liệu

VLOOKUP và HLOOKUP là những hàm tra cứu truyền thống, tìm giá trị trong cột hoặc hàng đầu tiên rồi trả về dữ liệu từ vị trí tương ứng. Mặc dù phổ biến, chúng có nhiều hạn chế về hướng tìm kiếm.

INDEX + MATCH khi kết hợp tạo thành công cụ tra cứu dữ liệu Excel linh hoạt hơn, cho phép tìm kiếm theo cả hai chiều và không bị giới hạn vị trí cột kết quả.

XLOOKUP là hàm hiện đại xuất hiện từ Excel 365/2021, khắc phục hầu hết nhược điểm của VLOOKUP với cú pháp đơn giản và khả năng tìm kiếm đa chiều.

Nhóm hàm lọc động

FILTER (có trong Microsoft 365 và Excel 2021) cho phép lọc toàn bộ bảng dữ liệu dựa trên một hoặc nhiều điều kiện, trả về mảng kết quả tự động cập nhật khi dữ liệu nguồn thay đổi. Kết hợp với UNIQUE giúp loại bỏ các giá trị trùng lặp trong kết quả. Để kiểm soát đầu vào dữ liệu hiệu quả hơn, bạn có thể tham khảo thêm cách dùng Data Validation trong Excel để tạo danh sách thả xuống và hạn chế dữ liệu nhập sai.

Bảng So Sánh Các Hàm Lookup Excel

Trước khi đi vào chi tiết từng hàm, hãy xem tổng quan để chọn đúng hàm cho từng tình huống:

Hàm Mục đích Ưu điểm Nhược điểm
VLOOKUP Tra cứu theo cột Dễ dùng, phổ biến rộng rãi Chỉ tìm từ trái sang phải, dễ lỗi khi chèn cột
INDEX + MATCH Tra cứu linh hoạt Tìm hai chiều, không giới hạn vị trí cột Cú pháp phức tạp hơn với người mới
XLOOKUP Tra cứu hiện đại Mạnh nhất, cú pháp đơn giản, xử lý lỗi sẵn Chỉ có trên Excel 365/2021 trở lên
SUMIF / SUMIFS Tính tổng có điều kiện Nhanh, tương thích mọi phiên bản Excel Chỉ tính tổng, không trả giá trị cụ thể
FILTER Lọc toàn bộ bảng Trả về mảng kết quả động, tự cập nhật Chỉ có trên Excel 365/2021 trở lên

Mẹo: Nếu đang dùng Excel 365 hoặc 2021, ưu tiên dùng XLOOKUP và FILTER vì chúng mạnh hơn và ít gặp lỗi hơn so với VLOOKUP.

Hướng Dẫn Chi Tiết Từng Hàm

Hàm INDEX - Truy Xuất Giá Trị Chính Xác Từ Phạm Vi Dữ Liệu

Hàm INDEX trả về giá trị tại vị trí cụ thể trong một mảng dữ liệu. Đây là lựa chọn lý tưởng khi bạn cần lấy dữ liệu từ bảng đa chiều mà không bị giới hạn bởi cấu trúc cột.

Cú pháp:

=INDEX(array, row_num, column_num)

  • array: Phạm vi ô chứa dữ liệu cần tra cứu (ví dụ: A1:D10)
  • row_num: Số thứ tự hàng chứa giá trị mong muốn
  • column_num: Số thứ tự cột chứa giá trị

Hàm Index

Hàm Index

Ví dụ thực tế:

Giả sử bạn có bảng dữ liệu từ A1 đến D10 chứa thông tin thành phố và dân số. Để lấy tên thành phố ở hàng 6, cột 3:

=INDEX(A1:D10, 6, 3)

Khi bảng dữ liệu có nhiều cột, việc giãn dòng trong Excel giúp dữ liệu dễ đọc hơn, đặc biệt khi làm việc với các mảng INDEX lớn.

Hàm MATCH - Xác Định Vị Trí Giá Trị Trong Phạm Vi

Hàm MATCH chuyên dùng để tìm vị trí của một giá trị trong dãy dữ liệu. Thường kết hợp với INDEX để bù đắp hạn chế của các hàm tra cứu khác.

Cú pháp:

=MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value: Giá trị cần tìm vị trí
  • lookup_array: Phạm vi chứa giá trị tra cứu
  • match_type: 0 = tìm chính xác | 1 = lớn nhất nhỏ hơn/bằng | -1 = nhỏ nhất lớn hơn/bằng

Hàm Match

Hàm Match

Ví dụ thực tế:

Trong dãy E8:E13 chứa các điểm số, để tìm vị trí của giá trị 30:

=MATCH(30, E8:E13, 0)

Tại TechCare, chúng tôi thường dùng MATCH trong báo cáo kiểm kê linh kiện laptop để định vị nhanh mã sản phẩm trong bảng dữ liệu lớn.

Hàm VLOOKUP - Tra Cứu Dọc Và Trả Về Giá Trị Tương Ứng

VLOOKUP là hàm tra cứu theo cột kinh điển, thường áp dụng trong danh sách nhân viên, sản phẩm hoặc đơn hàng có cấu trúc rõ ràng.

Cú pháp:

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

  • lookup_value: Giá trị dùng để dò tìm
  • table_array: Bảng dữ liệu (cố định bằng F4 để tránh lỗi khi sao chép)
  • col_index_num: Thứ tự cột cần lấy dữ liệu
  • range_lookup: TRUE (1) cho tra cứu gần đúng, FALSE (0) cho chính xác

Hàm Vlookup

Hàm Vlookup

Ví dụ thực tế:

Để lấy thông tin từ bảng G8:H11 dựa trên mã ở D4:

=VLOOKUP(D4, $G$8:$H$11, 2, 0)

Hàm này hiệu quả cho dữ liệu sắp xếp theo cột đầu tiên. Với bảng lớn hoặc cần tìm kiếm linh hoạt hơn, hãy chuyển sang XLOOKUP (nếu dùng Excel 365/2021). Sau khi tra cứu xong, nếu cần xử lý thêm số liệu, tham khảo cách tính phần trăm trong Excel để phân tích tỷ lệ từ dữ liệu vừa trích xuất.

Cách Lấy Dữ Liệu Từ Sheet Khác Trong Excel

Khi làm việc với nhiều sheet trong một file Excel, việc lấy dữ liệu từ bảng này sang bảng khác giúp đồng bộ thông tin mà không cần copy-paste thủ công.

Dùng VLOOKUP lấy dữ liệu từ sheet khác

Thêm tên sheet vào tham chiếu table_array. Ví dụ, lấy dữ liệu từ Sheet2:

=VLOOKUP(D4, Sheet2!$G$8:$H$11, 2, 0)

Dùng MATCH xác định vị trí từ sheet khác

Chỉ định tên sheet trong lookup_array. Ví dụ, tìm vị trí giá trị 30 trong Sheet3:

=MATCH(30, Sheet3!E8:E13, 0)

Dùng INDEX truy xuất linh hoạt từ sheet khác

INDEX đặc biệt mạnh khi kết hợp với tham chiếu chéo sheet. Ví dụ, lấy dữ liệu từ Sheet4:

=INDEX(Sheet4!C7:E13, 6, 2)

Lưu ý chung: Luôn kiểm tra đường dẫn sheet (Sheet1!) và dùng tên sheet thay vì số thứ tự nếu file có nhiều tab. Nếu file quá lớn, xem xét dùng Power Query để tối ưu hiệu suất.

Kinh Nghiệm Và Mẹo Sử Dụng

Tối ưu hiệu suất công thức

  • Hạn chế dùng SUMIFS với vùng dữ liệu trên 100 nghìn hàng - cân nhắc Pivot Table hoặc Power Query. Nếu file bắt đầu chậm, xem ngay hướng dẫn xử lý khi file Excel quá nặng để tối ưu hiệu suất.
  • Tránh lồng ghép quá nhiều hàm trong một công thức. Nên tách thành nhiều cột trung gian.
  • Dùng Name Manager đặt tên cho vùng dữ liệu hay dùng - công thức sẽ dễ đọc và dễ bảo trì hơn.

Xử lý điều kiện phức tạp

  • Điều kiện dạng 'hoặc' trong cùng một cột: dùng SUM kết hợp nhiều SUMIF thay vì SUMIFS.

=SUMIF(B:B,"Hà Nội",C:C)+SUMIF(B:B,"Đà Nẵng",C:C)

  • Điều kiện văn bản không phân biệt hoa thường: thêm dấu sao (*), ví dụ "*hà nội*".
  • Điều kiện ngày tháng: dùng hàm DATE để tạo tiêu chí, ví dụ ">"&DATE(2024,1,1).

Kết hợp nhiều hàm hiệu quả

  • INDEX-MATCH + IFERROR: tạo công thức tra cứu an toàn, tự xử lý trường hợp không tìm thấy.
  • XLOOKUP lồng trong FILTER: tạo bảng tra cứu động lọc theo khách hàng rồi trả thêm thông tin từ bảng khác.
  • SUMIFS trong IFERROR: trả về 0 thay vì lỗi khi không có dữ liệu thỏa mãn.

Khắc Phục Lỗi Thường Gặp Khi Tra Cứu Dữ Liệu Excel

Trong quá trình dùng các hàm tra cứu, bạn sẽ không tránh khỏi một số lỗi phổ biến. Dưới đây là cách xử lý nhanh — hoặc bạn có thể xem tổng hợp đầy đủ hơn tại bài các lỗi thường gặp trong Excel và cách khắc phục.

Lỗi #N/A

Nguyên nhân phổ biến nhất: giá trị tra cứu không tồn tại trong vùng tìm kiếm, có dấu cách thừa, ký tự ẩn hoặc định dạng khác biệt giữa hai giá trị.

Giải pháp:

  • Dùng TRIM() để loại dấu cách thừa, CLEAN() để xóa ký tự ẩn.
  • Bọc công thức trong IFERROR để hiển thị thông báo tùy chỉnh thay vì lỗi.

Lỗi #VALUE!

Thường xuất hiện khi vùng điều kiện và vùng tính toán không cùng kích thước trong SUMIFS/AVERAGEIFS, hoặc dùng phép toán trong tiêu chí không đúng cú pháp.

Giải pháp:

  • Đảm bảo tất cả vùng có cùng số hàng.
  • Tiêu chí có phép toán phải trong dấu ngoặc kép và nối bằng &. Ví dụ: ">"&E2, không phải >E2.

Lỗi #SPILL!

Xuất hiện trong Excel 365 khi vùng kết quả của FILTER hoặc UNIQUE bị chặn bởi dữ liệu có sẵn.

  • Giải pháp: Xóa sạch các ô bên phải và bên dưới ô chứa công thức, hoặc di chuyển đến vùng trống rộng hơn.

Kết quả không chính xác

  • Kiểm tra định dạng dữ liệu: số lưu dạng văn bản sẽ không tính đúng trong SUMIFS. Dùng VALUE() để chuyển đổi.
  • Phân biệt tham chiếu tương đối và tuyệt đối ($) khi sao chép công thức. 

Câu Hỏi Thường Gặp (FAQ)

Dùng XLOOKUP - nó mạnh hơn, đơn giản hơn và xử lý được các tình huống mà VLOOKUP không làm được (tìm từ phải sang trái, trả về nhiều cột cùng lúc, xử lý lỗi tích hợp sẵn).
Dùng hàm FILTER (Excel 365/2021) hoặc kết hợp SUMIFS/COUNTIFS cho tính toán tổng hợp. Với dữ liệu cần trả về cả hàng thỏa điều kiện, FILTER là lựa chọn tốt nhất.
Vì INDEX + MATCH không bị giới hạn hướng tìm kiếm (có thể tìm từ phải sang trái), không bị lỗi khi chèn/xóa cột giữa bảng, và nhanh hơn VLOOKUP với bảng dữ liệu lớn.
Có. Bạn có thể tham chiếu đến file Excel khác bằng cú pháp: =[TenFile.xlsx]Sheet1!A1. Tuy nhiên, file nguồn cần được mở để cập nhật dữ liệu theo thời gian thực, hoặc dùng Power Query để kết nối ổn định hơn.

Việc thành thạo các hàm lấy dữ liệu có điều kiện trong Excel là nền tảng không thể thiếu - từ tính tổng doanh thu theo khu vực đến xây dựng dashboard tự động cập nhật.

Bạn đang gặp lỗi cụ thể nào trong Excel? Hãy để lại comment để TechCare hỗ trợ chi tiết!