in ,

Cách sử dụng INDEX và MATCH trong Microsoft Excel

Logo Microsoft Excel trên nền xanh

Mặc dù hàm VLOOKUP rất tốt để tìm các giá trị trong Excel, nhưng nó có những hạn chế. Thay vào đó, với sự kết hợp của các hàm INDEX và MATCH, bạn có thể tra cứu các giá trị ở bất kỳ vị trí hoặc hướng nào trong bảng tính của mình.

Hàm INDEX trả về giá trị dựa trên vị trí bạn nhập trong công thức trong khi hàm MATCH thực hiện ngược lại và trả về vị trí dựa trên giá trị bạn nhập. Khi bạn kết hợp các chức năng này, bạn có thể tìm thấy bất kỳ số hoặc văn bản nào bạn cần.

VLOOKUP so với INDEX và MATCH

Sự khác biệt giữa các hàm này và VLOOKUP là VLOOKUP tìm các giá trị từ trái sang phải. Do đó tên của chức năng; VLOOKUP thực hiện tra cứu theo chiều dọc.

Microsoft giải thích rõ nhất cách thức hoạt động của VLOOKUP:

Có một số hạn chế nhất định khi sử dụng VLOOKUP—hàm VLOOKUP chỉ có thể tra cứu một giá trị từ trái sang phải. Điều này có nghĩa là cột chứa giá trị mà bạn tra cứu phải luôn nằm ở bên trái của cột chứa giá trị trả về.

Microsoft tiếp tục nói rằng nếu trang tính của bạn không được thiết lập theo cách mà VLOOKUP có thể giúp bạn tìm thấy những gì mình cần, thì bạn có thể sử dụng INDEX và MATCH để thay thế. Vì vậy, hãy xem cách sử dụng INDEX và MATCH trong Excel.

Khái niệm cơ bản về Hàm INDEX và MATCH

Để sử dụng các chức năng này cùng nhau, điều quan trọng là phải hiểu mục đích và cấu trúc của chúng.

Cú pháp của INDEX trong Dạng mảng là INDEX(mảng, số_hàng, số_cột) với hai đối số đầu tiên là bắt buộc và đối số thứ ba là tùy chọn.

INDEX tra cứu một vị trí và trả về giá trị của nó. Để tìm giá trị ở hàng thứ tư trong phạm vi ô từ D2 đến D8, bạn sẽ nhập công thức sau:

=INDEX(D2:D8,4)

Hàm INDEX trong Excel

Kết quả là 20,745 vì đó là giá trị ở vị trí thứ tư trong phạm vi ô của chúng ta.

Để biết thêm chi tiết về Mảng và Dạng tham chiếu của INDEX cũng như các cách khác để sử dụng hàm này, hãy xem hướng dẫn của chúng tôi về INDEX trong Excel.

Cú pháp của MATCH là MATCH(value, array, match_type) với hai đối số đầu tiên là bắt buộc và đối số thứ ba là tùy chọn.

MATCH tra cứu một giá trị và trả về vị trí của nó. Để tìm giá trị trong ô G2 trong phạm vi từ A2 đến A8, bạn sẽ nhập công thức sau:

=MATCH(G2,A2:A8)

Hàm MATCH trong Excel

Kết quả là 4 vì giá trị trong ô G2 nằm ở vị trí thứ tư trong phạm vi ô của chúng tôi.

Để biết thêm chi tiết về đối số match_type và các cách khác để sử dụng hàm này, hãy xem hướng dẫn của chúng tôi về MATCH trong Excel.

Cách sử dụng INDEX và MATCH trong Excel

Bây giờ bạn đã biết chức năng của từng chức năng và cú pháp của nó, đã đến lúc đưa bộ đôi năng động này vào hoạt động. Dưới đây, chúng tôi sẽ sử dụng cùng một dữ liệu như trên cho INDEX và MATCH riêng lẻ.

Bạn sẽ đặt công thức của hàm MATCH bên trong công thức của hàm INDEX tại vị trí cần tra cứu.

Để tìm giá trị (doanh số) dựa trên ID vị trí, bạn sẽ sử dụng công thức sau:

=INDEX(D2:D8,MATCH(G2,A2:A8))

Kết quả là 20,745. MATCH tìm giá trị trong ô G2 trong phạm vi từ A2 đến A8 và cung cấp giá trị đó cho INDEX tìm kiếm kết quả từ các ô từ D2 đến D8.

INDEX và MATCH cho tham chiếu ô

Hãy xem xét một ví dụ khác. Chúng tôi muốn biết thành phố nào có doanh số phù hợp với một số tiền nhất định. Sử dụng trang tính của chúng tôi, bạn sẽ nhập công thức này:

=INDEX(B2:B8,MATCH(G5,D2:D8))

Kết quả là Houston. MATCH tìm giá trị trong ô G5 trong phạm vi từ D2 đến D8 và cung cấp giá trị đó cho INDEX tìm kiếm kết quả từ các ô từ B2 đến B8.

INDEX và MATCH cho tham chiếu ô

Đây là một ví dụ sử dụng giá trị thực thay vì tham chiếu ô. Chúng tôi sẽ tìm kiếm giá trị (doanh số bán hàng) cho một thành phố cụ thể bằng công thức sau:

=INDEX(D2:D8,MATCH(“Houston”,B2:B8))

Trong công thức MATCH, chúng ta đã thay thế tham chiếu ô chứa giá trị tra cứu bằng giá trị tra cứu thực tế của “Houston” từ B2 đến B8, cho chúng ta kết quả 20.745 từ D2 đến D8.

Lưu ý: Hãy chắc chắn rằng khi bạn sử dụng giá trị thực để tra cứu, thay vì tham chiếu ô, bạn phải đặt giá trị đó trong dấu ngoặc kép như minh họa ở đây.

INDEX và MATCH cho văn bản

Để có được kết quả tương tự bằng cách sử dụng ID vị trí thay vì thành phố, chúng tôi chỉ cần thay đổi công thức thành:

=INDEX(D2:D8,MATCH(“2B”,A2:A8))

Ở đây, chúng tôi đã thay đổi công thức MATCH để tra cứu “2B” trong phạm vi ô từ A2 đến A8 và cung cấp kết quả đó cho INDEX, sau đó trả về 20.745.

INDEX và MATCH cho văn bản

Các chức năng cơ bản trong Excel như những chức năng giúp bạn cộng số trong ô hoặc nhập ngày hiện tại chắc chắn rất hữu ích. Nhưng khi bạn bắt đầu thêm nhiều dữ liệu hơn và nâng cao nhu cầu nhập hoặc phân tích dữ liệu của mình, các hàm tra cứu như INDEX và MATCH trong Excel có thể khá hữu ích.