Cách kết hợp hàm OFFSET và hàm MATCH để tìm kiếm nhiều điều kiện

Có thể nhiều bạn đã biết cách sử dụng hàm OFFSET. Nhưng các bạn đã biết kết hợp hàm OFFSET với hàm MATCH để tìm kiếm theo nhiều điều kiện cùng lúc chưa? Hãy cùng chúng mình tìm hiểu trong bài học Excel online dưới đây nhé.

Lý thuyết về hàm OFFSET và hàm MATCH

Hàm OFFSET

Hàm OFFSET trong Excel được sử dụng khi chúng ta cần xem hoặc tính toán dữ liệu của một ô hoặc một dãy các ô trong bảng tính dựa vào một vùng tham chiếu có sẵn.

Cú pháp của hàm OFFSET có dạng như sau: 

=OFFSET(reference, rows, cols, height, width)

Trong đó:

• Reference: là vùng tham chiếu làm cơ sở cho hàm (làm điểm xuất phát) để tạo vùng tham chiếu mới.
• Rows: là số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference. 
• Cols: là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.
• Height: là số dòng của vùng tham chiếu cần trả về. Height phải là số dương.
• Width: là số cột của vùng tham chiếu cần trả về. Width phải là số dương.

Hàm MATCH

Hàm MATCH trong Excel là một hàm tìm kiếm có tác dụng tìm một mục được chỉ định trong phạm vi của ô, sau đó trả về vị trí tương đối của mục đó trong phạm vi này.

Cú pháp của hàm này có dạng như sau:

Trong đó bao gồm các thành phần là:

• lookup_value: Giá trị cần tìm (tìm kiếm theo giá trị nào?). Giá trị này có thể là số, text, logic hoặc tham chiếu ô.
• lookup_array: Vùng tìm kiếm (tìm kiếm ở đâu?). Bạn có thể dò tìm trên 1 mảng (array) hoặc 1 vùng ô (range).
• match_type: Phương pháp tìm kiếm (tham số không bắt buộc). Có 3 phương pháp tìm kiếm:
• Không nhấp tham số hoặc nhập "1": Tìm kiếm tương đối theo giá trị nhỏ hơn gần nhất. 
• Nhập "-1": Tìm kiếm tương đối theo giá trị lớn hơn gần nhất. Chú ý: vùng tìm kiếm cần được sắp xếp theo thứ tự giảm dần, nghĩa là, từ giá trị lớn nhất đến giá trị nhỏ nhất hoặc từ Z tới A.
• Nhập số 0: Tìm chính xác theo giá trị lookup_value mà nó tìm được.

Vậy khi chúng ta kết hợp hàm MATCH và hàm OFFSET sẽ mang lại kết quả như thế nào? Hãy cùng thử sức với bài tập ở ngay dưới đây để biết nhé.

Xem thêm: Hướng dẫn cách dùng hàm OFFSET để tạo danh sách động trong Excel

Bài tập kết hợp hàm OFFSET và hàm MATCH

Đề bài

Cho 2 bảng dữ liệu như sau:

Yêu cầu: Xác định đơn giá cho từng mặt hàng được chọn trong bảng báo giá dựa theo mã sản phẩm ở cột B và size sản phẩm ở cột C. Trong đó mã sản phẩm và size sản phẩm được xác định dựa vào bảng danh mục hàng hóa.

Xem thêm: Đăng ký khóa học sau để thành thạo các chức năng và hàm Excel từ cơ bản đến nâng cao:

Cách thực hiện

Bước 1: Nhập công thức hàm OFFSET vào ô D6 (Cột đơn giá) trong bảng báo giá.

Bước 2: Chuyển sang bảng danh mục hàng hóa, chọn ô A3 làm gốc tọa độ. Chúng ta nên chọn vị trí đầu tiên trong bảng làm gốc là tốt nhất. Các bạn nhớ cố định tọa độ của ô A3 để khi đến bước sau có thể copy công thức xuống dòng khác nhé.

Bước 3: Lồng hàm MATCH vào công thức để tìm số dòng sẽ được di chuyển (rows trong hàm OFFSET), lấy lookup_value là ô B6 của bảng báo giá. Sau đó các bạn tiếp tục chuyển sang bảng danh mục hàng hóa và nhập tham chiếu mã sản phẩm là từ ô A4:A19.

Đừng quên cố định tọa độ cho vùng tham chiếu A4:A19 nhé:

Bởi vì mã sản phẩm của chúng ta có định dạng là Text nên tham số match_type chúng ta sẽ nhập là số 0 để tìm kiếm chính xác. Sau đó các bạn đóng hàm MATCH lại.

Bước 4: Lồng hàm MATCH vào công thức để tìm số cột sẽ được di chuyển (cols trong hàm OFFSET). Chúng ta sẽ lấy lookup_value cho hàm MATCH là ô C6 trong bảng báo giá. Sau đó các bạn chuyển sang bảng danh mục hàng hóa, chọn vùng tham chiếu là B3:E3.

Đừng quên cố định tọa độ cho vùng tham chiếu và nhập match_type là 0 để tìm kiếm chính xác nhé.

Bước 5: Thêm dấu ngoặc để đóng công thức hàm lại và bấm Enter để thu được kết quả.

Các bạn đối chiếu kết quả chúng ta tính được ở bảng báo giá với bảng danh mục hàng hóa ban đầu sẽ thấy đây là kết quả chính xác. Sau đó các bạn sao chép công thức xuống các dòng dưới là được nhé.

Bước 6: Đây là một bước tùy chọn. Các bạn có thể lựa chọn làm hoặc không làm. Nếu trong bảng báo giá của bạn có dòng trống thì khi sao chép công thức xuống sẽ hiển thị lỗi #N/A như thế này.

Chúng ta chỉ cần thêm một biện luận vào công thức hàm OFFSET ở bước 5 để nếu gặp ô trống, không có giá trị mã sản phẩm hay size thì không hiển thị lỗi #N/A. Chắc hẳn các bạn cũng đã biết là chúng ta nên lồng hàm IF và hàm OR vào đây. Công thức chi tiết như sau:

Tất nhiên, khi lồng thêm hàm thì các bạn cần thêm một dấu ngoặc đơn ở cuối công thức thì mới thực hiện được. Công thức cuối cùng có dạng như sau:

Kết quả mà chúng ta thu được như sau:

Xem thêm: Hướng dẫn cách viết hàm OFFSET trong VBA Excel hiệu quả

Kết luận

Qua bài viết này các bạn có thể thấy rằng chỉ cần biết cách kết hợp các hàm quen thuộc là chúng ta có thể xử lý được một vấn đề phức tạp.