Thông thường để tìm dữ liệu theo cột ta sử dụng hàm VLOOKUP, theo hàng thì sử dụng hàm HLOOKUP. Vậy khi tìm dữ liệu theo cả hàng và cột thì sử dụng hàm nào? Để tìm dữ liệu theo hàng và cột thì ta có thể sử dụng các hàm sau:
- Hàm INDEX kết hợp hàm MATCH:
=INDEX(data_range, MATCH(row_key, row_header_range, 0), MATCH(col_key, col_header_range, 0)) - Hàm XLOOKUP lồng nhau:
=XLOOKUP(row_key, row_header_range, XLOOKUP(col_key, col_header_range, data_range)) - Hàm INDEX kết hợp hàm XMATCH:
=INDEX(data_range, XMATCH(row_key, row_header_range, 0), XMATCH(col_key, col_header_range, 0)) - Hàm SUMPRODUCT:
=SUMPRODUCT((row_header_range=row_key)*(col_header_range=col_key)*data_range)
Trong cách cách trên, việc sử dụng hàm INDEX và MATCH là được sử dụng nhiều nhất. Dưới đây sẽ là hướng dẫn cụ thể và chi tiết cho từng cách và thực hành với bảng dữ liệu sau:
Tải ngay file dữ liệu mẫu kèm công thức hàm tìm dữ liệu theo hàng và cột
Trong bảng dữ liệu mẫu trên có:
- Dữ liệu gồm:
- Cột A: Sản phẩm (Cà phê, Trà, …)
- Hàng 2: Tháng (Jan…Dec)
- Ma trận số liệu: doanh số theo từng sản phẩm & tháng.
- Vùng tên đã đặt:
Products = Data!$A$3:$A$7Months = Data!$B$2:$M$2Matrix = Data!$B$3:$M$7
Sau đây sẽ là áp dụng chi tiết từ công thức chung đến thực tiễn:
Cách 1: Tìm dữ liệu theo hàng và cột với hàm INDEX + MATCH
Để tìm dữ liệu theo cột và hàng, ta thường sử dụng hàm INDEX kết hợp hàm MATCH với công thức chung sau:
=INDEX(data_range, MATCH(row_key, row_header_range, 0), MATCH(col_key, col_header_range, 0))
Trong đó:
- row_key: giá trị tra cứu theo hàng (ví dụ: tên sản phẩm).
- row_header_range: vùng tiêu đề hàng (danh sách sản phẩm).
- col_key: giá trị tra cứu theo cột (ví dụ: tên tháng).
- col_header_range: vùng tiêu đề cột (danh sách tháng).
- data_range: ma trận dữ liệu cần lấy kết quả.
Áp dụng với ví dụ của bài chẳng hạn như muốn tìm giá trị của tháng Jan với sản phẩm Cà phê thì ta sử dụng hàm sau:
=INDEX(Matrix, MATCH(B3, Products, 0), MATCH(B4, Months, 0))
Trong đó:
MATCH(B3, Products, 0)tìm vị trí hàng của sản phẩm chọn ở B3.MATCH(B4, Months, 0)tìm vị trí cột của tháng chọn ở B4.INDEX(Matrix, hàng, cột)trả về giao điểm.
Trong trường hợp có dữ liệu không tìm thấy, để tránh trả về lỗi công thức ta có thể sử dụng kết hợp hàm IFERROR để khi tìm dữ liệu không thấy sẽ trả về thông báo “Không tìm thấy” như sau:
=IFERROR(INDEX(Matrix, MATCH(B3, Products, 0), MATCH(B4, Months, 0)), "Không tìm thấy")
Cách 2: Sử dụng hàm XLOOKUP lồng nhau để tìm dữ liệu theo hàng và cột
Cách này áp dụng cho phiên bản Excel 365 trở lên, theo cách này công thức sẽ gọn hơn. Với cùng mục tiêu tìm giá trị của tháng Jan và sản phẩm cà phê thì ta sẽ áp dụng như sau:
Công thức tổng quát: =XLOOKUP(row_key, row_header_range, XLOOKUP(col_key, col_header_range, data_range))
Công thức áp dụng: =XLOOKUP(B3, Products, XLOOKUP(B4, Months, Matrix))
Trong đó:
XLOOKUP(B4, Months, Matrix)trả về toàn bộ cột tương ứng với tháng B4 (một mảng dọc).- Sau đó,
XLOOKUP(B3, Products, <cột vừa tìm>)chọn đúng hàng (sản phẩm).
Ta cũng có thể cải tiến thêm để khi không tìm thấy kết quả sẽ trả về thông báo “Không tìm thấy” như sau:
=XLOOKUP(B3, Products, XLOOKUP(B4, Months, Matrix, "Không tìm thấy", 0), "Không tìm thấy", 0)
Đặc biệt là với XLOOUP thì kể cả bạn đảo thứ tự thì công thức vẫn hoạt động bình thường:
=XLOOKUP(B4, Months, XLOOKUP(B3, Products, Matrix))
Cách 3: Sử dụng kết hợp hàm INDEX với XMATCH để tìm dữ liệu
Hàm XMATCH là phiên bản mới của hàm MATCH áp dụng cho các phiên bản Excel 365 và 2010 trở đi. Cấu trúc hàm XMATCH là:
=XMATCH(row_key, row_header_range, match_mode)
Trong đó:
- match_mode: 0 (chính xác), -1 (nhỏ hơn kế tiếp), 1 (lớn hơn kế tiếp), 2 (ký tự đại diện).
- search_mode: 1 (từ đầu), -1 (từ cuối) — hữu ích khi cần lấy bản ghi mới nhất.
Vậy với ví dụ tìm dữ liệu của tháng Jan và sản phẩm Cà phê ta sử dụng công thức của hàm INDEX và XMATCH như sau:
Công thức tổng quát: =INDEX(data_range, XMATCH(row_key, row_header_range, 0), XMATCH(col_key, col_header_range, 0))
Công thức áp dụng: =INDEX(Matrix, XMATCH(B3, Products, 0), XMATCH(B4, Months, 0))
Cách 4: Sử dụng hàm SUMPRODUCT để tìm giao điểm
Một cách khác để ta có thể sử dụng để tìm dữ liệu theo hàng và cột bằng cách sử dụng hàm SUMPRODUCT để lọc điểm giao dữ liệu giữa 2 mảng. Cụ thể công thức tổng quan như sau:
=SUMPRODUCT((row_header_range=row_key)*(col_header_range=col_key)*data_range)
Áp dụng tiếp tục với ví dụ để tìm giá trị của tháng Jan và sản phẩm Cà phê ta sử dụng hàm như sau:
=SUMPRODUCT((Products=B3)*(Months=B4)*Matrix)
Trong đó:
- (Products=B3) tạo mảng {0;1;…} đánh dấu hàng trùng sản phẩm.
- (Months=B4) tạo mảng {0,0,1,…} đánh dấu cột trùng tháng.
- Nhân với Matrix để “lọc” đúng ô giao; SUMPRODUCT cộng phần còn lại → kết quả chính là ô cần lấy.
Lưu ý: Nếu dữ liệu không duy nhất (nhiều ô cùng thỏa mãn) thì SUMPRODUCT sẽ cộng dồn.
Công thức tìm kiếm dữ liệu mở rộng
Khi tìm dữ liệu, có thể ta sẽ muốn tìm dữ liệu của một mảng dữ liệu thỏa mãn ví dụ như: tìm tất cả giá trị các tháng của sản phẩm cà phê, lúc này ta có thể áp dụng hàm XLOOKUP như sau:
Trả kết quả theo hàng:
=XLOOKUP(row_key, row_header_range, data_range)
Ví dụ như trả tất cả giá trị các tháng của sản phẩm Cà phê theo hàng ngang ta sử dụng công thức sau:
=XLOOKUP(B3, Products, Matrix)
Trả kết quả theo cột:
=XLOOKUP(col_key, col_header_range, data_range)
Tương tự áp dụng với ví dụ trên ta có công thức sau:
=XLOOKUP(B4, Months, Matrix)
Như vậy, bài viết trên đã hướng dẫn các bạn cách kết hợp các hàm để dò tìm dữ liệu theo hàng và cột trong Excel. Hy vọng bài viết sẽ hữu ích với các bạn trong quá trình làm việc. Chúc các bạn thành công!
