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

Hàm INDEX và MATCH là công cụ phổ biến nhất trong Excel để thực hiện các tra cứu nâng cao hơn. Do INDEX và MATCH cực kỳ linh hoạt, bạn có thể thực hiện tra cứu theo chiều ngang và dọc, tra cứu 2 chiều, tra cứu phía bên trái, tra cứu phân biệt chữ hoa hay chữ thường và thậm chí là tra cứu dựa trên nhiều tiêu chí khác. Nếu bạn muốn cải thiện kỹ năng Excel của mình, bài viết về hàm INDEX nâng cao và MATCH nâng cao này sẽ 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ủa bạn. 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

Trong bài viết này, những kiến thức cơ bản về cú pháp và cách sử dụng của từng hàm sẽ được giới thiệu khái quát mà không đi sâu hơn, do bài viết 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 nâng cao và MATCH để tìm kiếm thay thế trong Excel thay cho hàm VLOOKUP.

Hàm INDEX nâng cao

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: Hãy cùng xem ví dụ dưới đây để hiểu rõ hơn cách sử dụng hàm này nhé.

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. Với:

Nếu là 1 hoặc bị bỏ qua: 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, cụ thể là từ nhỏ nhất đến lớn nhất..

Nếu là 0: Tìm giá trị đầu tiên chính xác bằng giá trị tra cứu. Trong sự kết hợp của hàm INDEX nâng cao và MATCH, hầu như bạn luôn cần một kết hợp tuyệt đối, vì vậy bạn đặt đối số thứ ba của hàm MATCH thành 0.

Nếu là -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, cụ thể là từ lớn nhất đến nhỏ nhất.

Lưu ý: Lúc đầu, tính hữu ích của hàm MATCH có thể không rõ ràng. Điều ta thật sự muốn biết đó chính là giá trị ô chứ không phải vị trí của giá trị trong dải ô.

Điều bạn cần ghi nhớ chính là vị trí tương đối của giá trị cần tìm (cụ thể là số thứ tự của cột hay/và hàng) chính là tất cả những gì bạn cần để nhập vào câu lệnh row_num hay/và column_num của hàm INDEX. Như đã giới thiệu ở trên, hàm INDEX có thể trả về giá trị ở giao điểm của hàng và cột xác định, nhưng nó không thể xác định một cách chính xác bạn muốn cột nào hàng nào.

Ví dụ minh họa: Hãy cùng xem ví dụ dưới đây để hiểu rõ hơn cách sử dụng hàm này nhé.

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 nâng cao và hàm MATCH trong Excel

Qua các kiến thức và ví dụ cơ bản trên, bạn đọc đã có thể biết các những hàm này hoạt động như thế nào phải không? Tóm lại, hàm INDEX tìm giá trị tra cứu theo số cột và hàng, và hàm MATCH cung cấp các số đó. Vậy nếu kết hợp 2 hàm này lại thì sao, hãy cùng xem ví dụ dưới đây nhé.

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:

Xem thêm: Cách ứng dụng hàm Index và Match nâng cao (phần 2) 

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ừ thông số thứ hai (row_num), thông số này chỉ rõ bạn muốn trả về giá trị từ hàng nào, rồi trở thành công thức đơn giản như sau =INDEX($D$2:$D$10,3).

Đơn giản có thể nói, công thức được đọc như sau: Tìm 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.

Và bạn sẽ có được kết quả hiển thị như hình trên.

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)

Thì bạn cũng có được kết quả tương tự, thế nhưng, ở ví dụ này, Gitiho muốn diễn giải cho bạn thấy cách kết hợp hàm INDEX và MATCH mà thôi. Các ví dụ khác bên dưới sẽ cho bạn thấy khả năng thật sự của sự kết hợp hàm INDEX và MATCH – điều này giúp bạn dễ dàng xử lý nhiều viễn cảnh phức tạp khi hàm VLOOKUP không xử lý được. Cùng đón đọc ở phần 2 của bài viết:

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 nâng cao và MATCH. 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.