CÁCH ỨNG DỤNG HÀM INDEX VÀ MATCH NÂNG CAO (PHẦN 1)

Hàm INDEX và MATCH trong Excel cho phép tra cứu nâng cao và linh hoạt hơn, bao gồm tìm kiếm theo chiều ngang và dọc, tra cứu 2 chiều, tra cứu phía bên trái, phân biệt chữ hoa/thường và nhiều tiêu chí khác.

Hôm nay, chúng tôi sẽ hướng dẫn bạn hàm INDEX và MATCH nâng cao để giúp bạn tìm hiểu và thực hành cách ứng dụng chúng trong công việc hàng ngày. Cùng tìm hiểu nhé!

Xem thêm: Hướng dẫn giải bài tập Excel với hàm INDEX và hàm MATCH chi tiết

Những điều cơ bản về hàm INDEX và MATCH nâng cao

Bài viết hôm nay tập trung phân tích khía cạnh ưu việt của việc kết hợp hàm INDEX và MATCH thay cho VLOOKUP để là các hàm tìm kiếm nâng cao trong Excel.

Hàm INDEX

Tính năng: Hàm INDEX trả về giá trị của một ô trong bảng dựa trên số cột và hàng.

Công thức hàm:

=INDEX(array, row_num, [column_num])

Trong đó,

• array: Một dải ô mà bạn muốn trả về một giá trị
• row_num: Số thứ tự của hàng trong dải ô (array) mà bạn muốn trả về một giá trị. Nếu tham số này được bỏ qua, thì column_num là giá trị bắt buộc.
• colum_num: Số thứ tự của cột trong dải ô (array) mà bạn muốn giá trị được trả về. Nếu  tham số này được bỏ qua, thì row_num là là giá trị bắt buộc.

Lưu ý: Nếu cả 2 tham số row_num và colum_num đều được sử dụng, thì hàm INDEX sẽ trả về giá trị ở ô là giao điểm của hàng và cột xác định.

Ví dụ minh họa: 

Giả sử bạn nhập công thức hàm như sau: 

=INDEX(B4:F12,1,2)

Nghĩa là, công thức tìm kiếm dải ô B4:F12 rồi trả về giá trị của ô ở hàng thứ nhất (1) và cột thứ hai (2), cụ thể là ô C4.

Khá đơn giản, phải không? Tuy nhiên, trong thực tế, bạn không biết mình cần trả về giá trị ở hàng nào và cột nào. Do đó, hàm MATCH là lựa chọn kết hợp tuyệt vời.

Xem thêm: Giới thiệu các tính năng hữu dụng của hàm INDEX trong Excel

Hàm MATCH

Tính năng: Hàm MATCH tìm kiếm giá trị cần tìm trong dải ô, rồi trả về vị trí tương đối của ô đó trong dải ô.

Công thức hàm:

=MATCH(lookup_value, lookup_array, [match_type])

Trong đó,

• lookup_value: Chữ sô hay chuỗi ký tự mà bạn tìm kiếm. Đây có thể là một giá trị, một tham chiếu ô hay một giá trị logic.
• lookup_array: Dải ô được tìm kiếm
• match_type: Thông số này nói cho hàm MATCH biết bạn muốn trả về sự phù hợp tuyệt đối hay sự phù hợp tương đối. 

1 (Mặc định): Tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu. Các mảng tra cứu (array) phải được sắp xếp theo thứ tự tăng dần

0: Tìm giá trị đầu tiên chính xác bằng giá trị tra cứu. 

-1: Tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị lookup_value. Các giá trị trong mảng (array) cần tìm phải được sắp xếp theo thứ tự giảm dần.

Lưu ý: Lúc đầu, tính hữu ích của hàm MATCH có thể không rõ ràng. Chúng ta cần biết giá trị của ô, không phải vị trí nó trong dải dữ liệu.

Nhưng quan trọng là giá trị cần tìm kiếm (chẳng hạn là số thứ tự của cột hoặc hàng) có thể cung cấp mọi thông tin cần thiết cho hàm INDEX khi chúng ta tiến hành kết hợp 2 hàm này để xác định vị trí hàng và cột của một giá trị.

Ví dụ minh họa: 

Giả sử nếu dải ô B1:B3 chứa các giá trị “New-York”, “Paris”, “London”, và bạn sử dụng công thức:

=MATCH("London",B1:B3,0)

Nghĩa là, công thức sẽ trả kết quả về số 3 bởi vì “London” là mục thứ ba trong dải ô (array) tra cứu.

Cách sử dụng hàm index và match nâng cao trong Excel

Công thức hàm kết hợp:

=INDEX (column to return a value from, MATCH (lookup value, column to lookup against, 0))

Ví dụ minh họa:

Giả sử bạn có một danh sách dữ liệu và bạn cần tra cứu dân số của Nhật Bản:

Bạn sử dụng công thức:

=INDEX($D$2:$D$10,MATCH(“Nhật Bản”,$B$2:$B$10,0))

Trong đó,

• Hàm MATCH tìm kiếm giá trị cần tìm “Nhật Bản” ở cột B, chính xác hơn là dải ô B2:B10,  rồi trả về số 3, bởi vì “Nhật Bản” nằm thứ ba trong danh sách.
• Hàm INDEX lấy số 3 từ kết quả của hàm MATCH làm tham số thứ hai (row_num), rồi trở thành công thức đơn giản như sau =INDEX($D$2:$D$10,3)

Công thức trên sẽ tìm dân số trong dải ô D2:D10 rồi trả về giá trị của ô ở hàng thứ 3, cụ thể là ô D4, bởi vì chúng ta bắt đầu đếm từ hàng thứ 2.

Lưu ý: Số hàng và số cột trong mảng (array) của hàm INDEX nên lần lượt khớp với số hàng và số cột trong thông số row_num hay/và column_num trong hàm MATCH. Nếu không thì, công thức sẽ trả về kết quả không chính xác.

Tại sao không sử dụng hàm VLOOKUP?

Nếu bạn sử dụng công thức sau: 

=VLOOKUP(“Nhật Bản”,$B$2:$D$2,3)

Khi dùng hàm VLOOKUP trong trường hợp này, chúng ta vẫn nhận được kết quả tương tự. Tuy nhiên, VLOOKUP có nhiều hạn chế hơn INDEX và MATCH khi dữ liệu cần xử lý phức tạp, chẳng hạn như:

• Hàm VLOOKUP chỉ hoạt động theo chiều ngang (tra cứu dựa trên cột). Điều này làm cho nó không thể tra cứu theo chiều dọc hoặc kết hợp cả hai chiều một cách linh hoạt.
• Bạn phải xác định số cột chứa dữ liệu cần trả về. Nếu bạn thêm hoặc xóa cột, công thức có thể bị lỗi.
• VLOOKUP yêu cầu cột tìm kiếm nằm bên trái cột chứa dữ liệu trả về.
• Hàm VLOOKUP thường chậm hơn khi làm việc với dữ liệu lớn so với việc sử dụng kết hợp INDEX và MATCH.
• VLOOKUP chỉ cho phép tra cứu dựa trên một tiêu chí duy nhất, trong khi INDEX MATCH có thể thực hiện tra cứu theo nhiều tiêu chí khác nhau.

Xem thêm: Vì sao dùng hàm INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel?

Tổng kết

Qua bài viết này, Gitiho hy vọng bạn đọc có thể hiểu được cách sử dụng căn bản và kết hợp của hàm INDEX và MATCH nâng cao. Hãy đón đọc phần tiếp theo của bài viết này với các ứng dụng của hàm INDEX kết hợp MATCH phần 2 nhé.

Ngoài ra, để thành thạo các thủ thuật của hàm ứng dụng vào công việc hằng ngày bạn nên tham khảo các hàm VLOOKUP, IF, AND và luyện tập các bài tập Excel có đáp án được cung cấp.