Đến đây bạn chắc chắn là một chuyên gia dùng hàm INDEX MATCH, chúng ta hoàn toàn thể sử dụng nó để thay thế hàm VLOOKUP. Nhưng vẫn còn một vài điều kiện tìm kiếm mà bạn không chắc chắn làm thế nào để thực hiện. Quan trọng nhất, bạn muốn có thể dò tìm giá trị dựa trên nhiều điều kiện trong các cột riêng biệt.

May mắn thay, có một giải pháp. Chúng ta có thể kết hợp INDEX MATCH với một tên gọi mới là”công thức mảng” (array formulas) để dò tìm một giá trị dựa trên nhiều điều kiện. Dưới đây là cách thực hiện.

Trước tiên, hãy làm quen với INDEX MATCH là điều kiện tiên quyết cho bạn dễ hiểu hướng dẫn này. Nếu bạn chưa cảm thấy chưa tự tin với bài viết này, hãy xem hướng dẫn INDEX MATCH cơ bản để bắt kịp. Và nếu bạn chỉ mới bắt đầu với Excel, hãy bắt đầu với hướng dẫn của chúng tôi về các tính năng Excel hàng đầu để tìm hiểu.

Nhận diện vấn đề

Bảng tính dưới đây liệt kê doanh số SnackWorld của cả Cookies và Brownies theo tháng. Bảng tính trình bày đơn giãn chỉ có hàng và cột, 3 cột Month, Item và Units sold (MM) là kết hợp giữa Month và Item nằm trên hàng riêng của nó.

Chúng ta muốn có thể dò tìm số lượng đơn vị được bán (Units sold MM) dựa trên sự kết hợp cụ thể của mặt hàng (Item) theo tháng (Month)— ví dụ: số lượng Cookie được bán trong tháng 2.

Sử dụng hàm MATCH với nhiều điều kiện

Để giải quyết vấn đề này, chúng ta sẽ phải tìm ra cách sử dụng hàm MATCH để khớp với nhiều cột điều kiện. Cách để giải quyết vấn đề này là sử dụng “công thức mảng”, có thể kết hợp nhiều điều kiện cùng một lúc.

Với MATCH, cách dễ nhất để tạo công thức mảng là sử dụng ký hiệu &, như sau:

=MATCH(lookup_value_1&lookup_value_2,lookup_array_1&lookup_array_2, match_type)

Điều rất quan trọng cần lưu ý là khi bạn sử dụng công thức mảng như thế này, bạn cần phải đảm bảo công thức là công thức mãng bằng cách sử dụng nhiều phím kết hợp thay vì chỉ nhấn Enter sau khi kết thức nhập công thức. Điều này sẽ cho Excel biết rằng bạn đang sử dụng công thức mảng thay vì công thức chuẩn. Dấu hiệu để bạn nhận dạng được một công thức mảng, Excel sẽ đặt công thức bên trong dấu ngoặc nhọn (curly braces) ({ }). Kết thúc nhập công thức bằng nhấn cùng lúc tổ hợp phím Ctrl+Shift+Enter

Chúng ta hãy xem một ví dụ, trong đó chúng ta so sánh khớp với hai cột riêng biệt: Month và Item:

{=MATCH("February"&"Brownies",B3:B8&C3:C8,0)}
Output: 4

Trong công thức này, chúng ta đã sử dụng ký hiệu & để yêu cầu MATCH tra cứu hai điều kiện thay vì một. Excel trả về giá trị 4, bởi vì trên hàng thứ tư từ trên xuống, nó trả về kết quả đúng đáp ứng cả hai điều kiện mà chúng ta yêu cầu: Cột B chứa từ “February” và Cột C chứa từ “Brownies”.

Lưu ý rằng thứ tự các điều kiện ở đây là quan trọng. Kể từ khi đối số “February” tìm thấy đầu tiên, nó nhìn lên trong vùng điều kiện chọn ở đây là – B3: B8. Tương tự như vậy, kể từ khi đối số “Brownies” được tìm thấy đứng thứ hai, nó được khớp trong vùng điều kiện chọn ở đây là C3: C8.

Nếu bạn gặp lỗi khi nhập công thức, hãy đảm bảo bạn đã kết hợp với sử dụng nhiều phím và xem có ký hiệu {} có trong thanh công thức. Excel sẽ báo lỗi nếu bạn chưa khai báo bạn đang nhập một công thức mảng. Khắc phục bằng cách nhấn đồng thời tổ hợp phím Ctrl+Shift+Enter

Kết hợp Match Index lại với nhau

Bây giờ chúng ta đã biết cách sử dụng MATCH với nhiều điều kiện, thật dễ dàng để đưa INDEX kết hợp vào công thức. Công thức cuối cùng của chúng ta sẽ trông giống như thế này:

{=INDEX(range, MATCH(lookup_value_1&lookup_value_2&..., lookup_range_1&lookup_range_2&...,match_type))}

Trước tiên, hãy sử dụng chức năng này để tìm hiểu tháng nào chúng ta đã bán được 76 triệu đơn vị Brownies:

{=INDEX(B3:B8,MATCH("Brownies"&76,C3:C8&D3:D8,0))}
Output: "February"

Tiếp theo, hãy tạo một số ô nhập động cho phép chúng ta nhập Month và Item, sau đó viết một công thức cho Excel biết để đưa số lượng đơn vị được bán dựa trên sự kết hợp Month và Item. Công thức INDEX MATCH của chúng ta sẽ tham chiếu các ô động này với các đối số tìm kiếm lookup_value:

{=INDEX(D3:D8,MATCH(G2&G3,B3:B8&C3:C8,0))}
Month input: "March" (G2)
Item input: "Cookies" (G3)
Output: 29

Vậy là chúng ta đã thực hiện thành công tìm kiếm kết hợp – INDEX MATCH với nhiều điều kiện! Lưu ý rằng chúng ta đang bị giới hạn ở hai điều kiện khi thực hiện các ví dụ nêu trên. Bạn có thể thêm bao nhiêu tiêu chí bổ sung tùy thích bằng cách sử dụng ký hiệu & nhiều lần giữa các điều kiện. Cần lưu ý rằng hiệu suất Excel có thể chậm lại nếu bạn kết hợp nối chuỗi điều kiện quá nhiều trong cùng một lúc trong một bảng tính lớn có nhiêu dữ liệu.

Hi vọng, sau khi đọc xong bài viết ngắn này, các bạn có thể áp dụng hàm MATCH INDEX phục vụ công việc của mình một cách hiệu quả và nhanh chóng. Bạn còn thắc mắc, comment bên dưới chúng ta sẽ cùng giải quyết tiếp!!!

• Cách sử dụng hàm Match trong Excel (Match + Index/Vlookup)
• Hàm Excel thường được sử dụng (phần 1)
• Sách lập trình Macro VBA Excel – Writing Excel Macros with VBA 2nd
• Excel VBA: Xóa dòng Excel dựa trên điều kiện đã xác định
• Định dạng có điều kiện trong Excel